使用 INDEX 和 MATCH 進行多條件查找

在處理具有多列和行標題的 Excel 電子表格中的大型數據庫時，要找到符合多個條件的內容總是棘手的。在這種情況下，您可以使用包含 INDEX 和 MATCH 函數的陣列公式。

如何執行多條件查找？

為了找出如上圖所示的白色、中號且價格為 18 美元的產品，您可以利用布爾邏輯生成一個由 1 和 0 組成的陣列，以顯示符合條件的行。然後，MATCH 函數將找到符合所有條件的第一行的位置。之後，INDEX 將在同一行中找到相應的產品 ID。

通用語法

=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ 注意：這是一個陣列公式，需要您使用 Ctrl + Shift + Enter 輸入。

要找到白色、中號且價格為 18 美元的產品，請在單元格 H8 中複製或輸入以下公式，並按 Ctrl + Shift + Enter 以獲取結果：

=INDEX(B5:B10,MATCH(1,("White"=C5:C10)*("Medium"=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): match_type 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。

相關函數

相關公式