Vlookup在另一个表有多个标准 [英] Vlookup on another table with multiple criteria

问题描述

我有一个付款清单和一个表，承诺收集官员制作。

Hi, I have a list of payments and a table with the promises that collection officers made.

我想通过ID（和日期）将与债务人达成协议的上一个收款人员分配到付款表中。

I want assign into the payment table by id (and date) the last collection officer that made the agreement with the debtor.

例如：id 1111有两个承诺：Morticia Adams和Gulliver;但是，我选择格列佛，因为他在该日期范围内作出最后的承诺，考虑到付款日期。

For example: On id 1111 there were two promises made: by Morticia Adams and Gulliver; But i choose Gulliver because he made the last promise in that date range taking in account the date of payment.

同样对于id 5425，最后的承诺是由Marie Anne因为23.10.2016（付款日期）在12.10.2016和26.10.2016之间（承诺日期）。

Likewise for id 5425 the last promise made was made by Marie Anne because 23.10.2016 (date of payment) is between 12.10.2016 and 26.10.2016 (promise dates).

如果有人可以指导我，我将非常感激

I would be really grateful if somebody could guide me through this.

谢谢！

推荐答案

免责声明： OP描述，而不是图片显示。

一种方法是找到满足条件的最新对话日期：

Disclaimer: this formula generates what OP is describing, not what the picture is showing.

One way to do this is by finding the latest conversation date that satisfies the conditions:

• 承诺表中行的付款ID等于付款表的付款ID。


• 日期付款大于或等于会话日期。


• 付款日期小于或等于商定的付款日期。

哪个我们可以这样做：

{=MAX(($A$3:$A$8 = A12) * (B12 >= $B$3:$B$8) * (B12 <= $C$3:$C$8) * $B$3:$B$8)}

然后将日期与付款ID相结合，使用我们可以用来查找名称的唯一键。

And then combining the date with the payment ID to have a unique key that we can use to look up the name.

{=MAX(($A$3:$A$8 = A12) * (B12 >= $B$3:$B$8) * (B12 <= $C$3:$C$8) * $B$3:$B$8) & A12}

最后我们可以执行索引匹配，查找上述公式的输出

And lastly we can perform an Index match, looking up the output of the above formula in the concatenated range of date and payment ID in the promises table

{=INDEX(
    $D$3:$D$8,
    MATCH(
      MAX(($A$3:$A$8 = A14) * (B14 >= $B$3:$B$8) * (B14 <= $C$3:$C$8) * $B$3:$B$8) & A14,
      $B$3:$B$8 & $A$3:$A$8, 
      0))}

{} 表示您必须使用输入公式Ctrl + Shift + 输入

The {} means you have to enter the formula using Ctrl + Shift + Enter

请注意，这将会更加可读和直观你命名范围。

Note that this will be a lot more readable and intuitive if you name the ranges.

这篇关于Vlookup在另一个表有多个标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！