查找特定字符串时多条件索引匹配

Question

   A          B          C
1  Name       Last Name  ID
2  Ben        Dafflin    ID1001
3  Yu         Yiin       ID1002
5  Max        Gray       ID1003
6  John Carl  Flit       ID1004

Situation 1 : Index Match with wildcards "*" (Working Fine!)
Formula    : =INDEX($C:$C,MATCH("*John*",$A:$A,0))
Result      : ID1004


Situation 2 : Index Match with multiple criteria (Working Fine!)
Formula     : =IFERROR(INDEX(D:D,MATCH(1,(B:B="Flit")*(C:C="John Carl"),0)),"")
Result      : ID1004

问题:在情况2中，如果它只查找带有"Carl"或"*Carl*"的单元格，则无法工作。任何建议我将如何使用具有多个条件的索引匹配的情况2，但仍然可以查找包含此类特定字符串的单元格。

Answer 1

要让你的第二个公式返回值，你只需要引用正确的列：=IFERROR(INDEX(C:C,MATCH(1,(B:B="Flit")*(A:A="John Carl"),0)),"")

如果您想在具有多个条件的MATCH中使用通配符，您可以尝试：

=INDEX(C:C,MATCH(1,1/(ISNUMBER(SEARCH("*Carl*",A:A))*(B:B="Flit")),0))

如果您使用Office 365

=FILTER(C:C,ISNUMBER(SEARCH("*Carl*",A:A))*(B:B="Flit"))

Answer 2

与多列匹配时的查找

还有另一种方法，它更简单，也可能更适合：

{=INDEX(C:C,MATCH("*CarlFlit",A:A&B:B,0))}

当然，上面是一个数组公式(因此是开始的{和结束的})

a)匹配的数组是连接成单个字符串的列A和B的行

b)这个特定的例子当然是寻找(*通配符) *CarlFlit

c)当然，它可以是值和有效MATCH wildcards的任意组合

如果要查找的值在单元格中(而不是硬编码到公式中)，则只需引用这些单元格：

{=INDEX(C:C,MATCH(E2&F2,A:A&B:B,0))}

要添加对不匹配的检查(使用上面的第一个示例)：

{=IF(ISNUMBER(MATCH("*CarlFlit",A:A&B:B,0)),INDEX(C:C,MATCH("*CarlFlit",A:A&B:B,0)),"Not Found")}

附加信息

上面的公式可以是扩展的，以适应任意数量的列的匹配。

O只需在每个额外的列中执行& (以适合您的目的的顺序)。

出于performance的原因，让查找函数查看整个列并不是一个好主意。

a)更好地使用(例如) {=INDEX($C$1:$C$1000,MATCH("*CarlFlit",$A$1:$A$1000&$B$1:$B$1000,0))}。

b)其中1000当然是您查找范围内的示例last row。