使用INDEX和MATCH进行多条件查找

在处理具有多列和行标题的Excel电子表格中的大型数据库时，要找到符合多个条件的内容总是很棘手。在这种情况下，您可以使用带有INDEX 和MATCH 函数的数组公式。

如何执行多条件查找？

为了找出如上图所示价格为18美元、白色且中号的产品，您可以利用布尔逻辑生成一个由1和0组成的数组，以显示符合这些条件的行。然后，MATCH函数将找到满足所有条件的第一行的位置。之后，INDEX将找到同一行上的相应产品ID。

通用语法

=INDEX(返回范围,MATCH(1,(条件值1=条件范围1*条件值2=条件范围2*(…),0))

√ 注意：这是一个数组公式，需要您使用Ctrl + Shift + Enter输入。

要找到价格为18美元、白色且中号的产品，请复制或在单元格H8中输入以下公式，并按Ctrl + Shift + Enter获取结果：

=INDEX(B5:B10,MATCH(1,("白色"=C5:C10)*("中号"=D5:D10)*(18=E5:E10),0))

或者，使用单元格引用来使公式动态化：

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

公式解释

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): 公式将单元格h3中的颜色与范围C5:C10中的所有颜色进行比较；将H5中的尺寸与D5:D10中的所有尺寸进行比较；将H6中的价格与E5:E10中的所有价格进行比较。初始结果如下：{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}。乘法会将TRUE和FALSE转换为1和0：{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}。相乘后，我们将得到一个单一数组：{0;0;0;0;1;0}。
• MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): 匹配类型0要求MATCH函数查找精确匹配项。然后该函数将返回1在数组{0;0;0;0;1;0}中的位置，即 5。
• INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): INDEX函数返回产品ID范围B5:B10中的第5个值，即30005。

相关功能

相关公式