使用索引查找并与两个条件匹配-LookupusingINDEXandMATCHwithtwocriteria

4  

I suggest the conventional solution to problems of this kind is to concatenate the pair of search terms (ie a helper column) and to add the concatenated pairs to the lookup array.

我建议解决这类问题的传统方法是将搜索词对(即助手列)连接起来，并将连接对添加到查找数组中。

In the example above the concatenation of what to look up (rather than where to look up) is done 'on the fly'.

在上面的示例中，查找内容(而不是查找位置)的连接是“动态”完成的。

11  

You can use an "array formula" like this

您可以使用这样的“数组公式”

=INDEX('Sheet2'!B:B,MATCH(1,(A1='Sheet2'!A:A)*(C1='Sheet2'!C:C),0))

=指数(“Sheet2”! B:B,匹配(1,(A1 = ' Sheet2 ! A:A)*(C1 =“Sheet2”! C:C),0))

CTRL+SHIFT+ENTER

CTRL + SHIFT + ENTER

....or you can add another INDEX function so that it doesn't need to be "array entered", i.e.

....或者您可以添加另一个索引函数，这样它就不需要“输入数组”，例如。

=INDEX('Sheet2'!B:B,MATCH(1,INDEX((A1='Sheet2'!A:A)*(C1='Sheet2'!C:C),0),0))

=指数(“Sheet2”! B:B,匹配(1、索引((A1 = ' Sheet2 ! A:A)*(C1 =“Sheet2”! C:C),0),0))

or another way is to use LOOKUP like this

另一种方法是像这样查找

=LOOKUP(2,1/(A1='Sheet2'!A:A)/(C1='Sheet2'!C:C),'Sheet2'!B:B)

=查找(2,1 /(A1 = ' Sheet2 ! A:A)/(C1 =“Sheet2”! C:C),“Sheet2”! B:B)

That latter method would give you the last match if there is more than one......

如果有多个匹配项，后一种方法将给出最后一个匹配项

0  

Here is the solution without using an array and without using a helper column:

以下是不使用数组和不使用辅助列的解决方案:

=INDEX(Table[returnColumnName], 
MATCH(1, INDEX((Table[lookupColumn1] = "arraysAreSlow") * 
(Table[lookupColumn2] = "avoidWherePossible"), 0, 1), 0))

Here is a more advanced solution that performs a grid lookup:

下面是执行网格查找的更高级的解决方案:

=INDEX(Table,
MATCH(1, INDEX((Table[lookupColumn1] = "arraysAreSlow") * 
(Table[lookupColumn2] = "avoidWherePossible"), 0, 1), 0),
MATCH("returnColumnName", Table[#Headers],0))