跨多列使用 INDEX 和 MATCH 进行查找

如果需要在多列中匹配查找某个值，可以结合 INDEX 和 MATCH 函数，以及 MMULT、TRANSPOSE 和 COLUMN 等数组公式轻松实现。

如何跨多列匹配查找某个值？

如上表所示，要填写每位学生对应的班级，而班级信息分布在多列时，您可以先结合使用 MMULT、TRANSPOSE 和 COLUMN 函数生成矩阵数组，再用 MATCH 函数获取查找值的位置，最后将其传递给 INDEX 函数，轻松返回所需数组中的值。

通用语法

=INDEX(）return_range,(MATCH(1,MMULT(--(））））lookup_array=lookup_value),TRANSPOSE(COLUMN(）lookup_array)^0)),0)))

√ 注意：此为数组公式，输入时需同时按下 Ctrl+Shift+Enter 键。

要查找 Jimmy 所在的班级，请将下方公式复制或输入至 H5 单元格，并按下 Ctrl+Shift+Enter，即可得出结果：

=INDEX(）$B$5:$B$7,(MATCH(1,MMULT(--(））））$C$5:$E$7=G5),TRANSPOSE(COLUMN(）$C$5:$E$7)^0)),0)))

√ 注意：上面的美元符号（$） 表示绝对引用，即在将公式移动或复制到其他单元格时，公式中的名称和范围不会发生变化。请注意，代表查找值的单元格引用不应添加美元符号，因为复制公式到其他单元格时需要使其为相对引用。输入公式后，可以拖动填充柄向下，将公式应用到下方的单元格。

公式说明

=INDEX(）$B$5:$B$7,(MATCH(1,））MMULT(）--($C$5:$E$7=G5),TRANSPOSE(）COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): 此片段检测区域 $C$5:$E$7 中的每个值是否等于单元格 G5 中的值，并生成如下 TRUE 和 FALSE 的数组：
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  双负号操作会将 TRUE 和 FALSE 转换为 1 和 0，从而得到如下数组：
  {1,0,0;0,0,0;0,0,0}.
• COLUMN($C$5:$E$7):COLUMN 函数返回区域 $C$5:$E$7 的列号，结果如下所示：{3,4,5}.
• TRANSPOSE(）COLUMN($C$5:$E$7)^0)=TRANSPOSE(）{3,4,5}^0)： 将数组 {3,4,5} 中的每个数字都进行 0 次方运算后，都会变为 1，得到结果：{1,1,1}。接着，TRANSPOSE 函数会将该列数组转换为如下的行数组：{1;1;1}。
• MMULT(）--($C$5:$E$7=G5),TRANSPOSE(）COLUMN($C$5:$E$7)^0))=MMULT(）{1,0,0;0,0,0;0,0,0},{1;1;1}):MMULT 函数返回这两个数组的乘积，结果如下：{1;0;0}.
• MATCH(1,）MMULT(）--($C$5:$E$7=G5),TRANSPOSE(）COLUMN($C$5:$E$7)^0)),0)=MATCH(1,）{1;0;0},0)：match_type 0 会强制 MATCH 函数返回 1 在数组 {1;0;0} 中首次出现的位置，也就是 1.
• INDEX(）$B$5:$B$7,(MATCH(1,））MMULT(）--($C$5:$E$7=G5),TRANSPOSE(）COLUMN($C$5:$E$7)^0)),0))) = INDEX($B$5:$B$7INDEX 函数返回班级区域 $B$5:$B$7 中第 1 个值，即 A。

如果您想轻松地跨多列匹配查找某个值，也可以使用我们的专业 Excel 加载项 Kutools For Excel。请参阅此处的操作说明以完成目标。

相关函数

相关公式