跨多列的 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): 將次方提升為 0 後，陣列 {3,4,5} 中的所有數字都將轉換為 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): match_type 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。  請參閱此處的說明以完成任務。

相關函數

相關公式