跨多列的INDEX和MATCH函数

要通过跨多列匹配来查找值，可以使用基于 INDEX 和 MATCH 函数的数组公式，并结合 MMULT、TRANSPOSE 和 COLUMN 函数来实现。

如何通过跨多列匹配来查找值？

为了根据上表所示填写每个学生的对应班级信息（其中信息分布在多列中），您可以首先利用 MMULT、TRANSPOSE 和 COLUMN 函数生成一个矩阵数组。然后，MATCH 函数将为您提供查找值的位置，该位置将传递给 INDEX 以检索您在数组中寻找的值。

通用语法

=INDEX(返回区域,(MATCH(1,MMULT(--(查找区域=查找值),TRANSPOSE(COLUMN(查找区域)^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(TRANSPOSE(COLUMN($C$5:$E$7)^0)^0) = TRANSPOSE(TRANSPOSE({3,4,5}^0)^0): 将数组 {3,4,5} 中的所有数字提升为 0 次幂后，它们都会转换为 1：{1,1,1}。TRANSPOSE 函数随后将列数组转换为行数组，如下所示：{1;1;1}。
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPOSE(COLUMN($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): MMULT 函数返回两个数组的矩阵乘积，如下所示：{1;0;0}。
• MATCH(1,MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0),0) = MATCH(1,MATCH(1,{1;0;0},0),0): 匹配类型 0 强制 MATCH 函数返回数组 {1;0;0} 中第一个匹配 1 的位置，即 1。
• INDEX($B$5:$B$7$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$B$5:$B$7,,,1): INDEX 函数返回班级范围 $B$5:$B$7 中的第 1 个值，即 A。

为了轻松地通过跨多列匹配查找值，您还可以使用我们的专业 Excel 插件 Kutools For Excel。  请参阅此处的说明以完成任务。

相关功能

相关公式