跨多欄使用 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)： 將數值次方提升至 0 後，陣列 {3,4,5} 中的所有數字都會轉換為 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;0;0} 中第一個匹配 1 的位置，該位置為 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。立即參閱此處說明，快速完成任務！。

相關函數

相關公式