Excel INDEX MATCH：基礎與進階查詢

在 Excel 中，精準擷取特定資料往往是日常作業的常見需求。雖然 INDEX 與 MATCH 函數各自都有獨到之處，但兩者一旦結合，便能激發出強大的資料查詢潛力。它們不僅能實現基本的橫向與縱向查詢，更能進一步支援雙向查詢、區分大小寫查詢，以及多重條件查詢等進階功能。相較於 VLOOKUP，INDEX 搭配 MATCH 提供了更靈活、更廣泛的資料查詢選項。在本教學中，我們將深入探索這對函數組合所能實現的各種可能性。

• INDEX 函數
• MATCH 函數
• 結合 INDEX 與 MATCH

• 雙向查詢
• 向左查詢
• 區分大小寫的查詢
• 最接近的相符項目
• 多重條件查詢
• 跨多欄查詢
• 查詢第一個非空白值
• 查找第一個數值
• 查詢 MAX 或 MIN 的關聯值
• 提示：自訂您的 #N/A 錯誤訊息

如何在 Excel 中使用 INDEX 與 MATCH

在使用 INDEX 與 MATCH 函數之前，先來了解這兩個函數如何協助您輕鬆進行數值查詢。

如何在 Excel 中使用 INDEX 函數

Excel 中的 INDEX 函數可傳回指定範圍中特定位置的值，其語法如下：

• array（必要）指您要從中傳回值的範圍。
• row_num（必要，除非存在 ）column_num）用於指定陣列的列號。
• column_num（選用，但若省略 ）row_num 則為必要）：指定陣列中的欄號。

例如，若要得知 Jeff 的分數，也就是名單上的第 6 位學生，您可以這樣使用 INDEX 函數：

=INDEX(C2:C11,6)

√ 注意：範圍 C2:C11 為列出分數的位置，而數字 6 則用來找出第 6 位學生的考試分數。

這裡讓我們做個小測試！針對公式 =INDEX(A1:C1,2)，它會傳回什麼值？——沒錯，就是該列中的第 2 個值：出生日期！

現在您已經了解，INDEX 函數能輕鬆應對橫向或縱向的資料範圍。但若要在包含多列與多欄的大型範圍中精準傳回特定值呢？此時，就必須同時指定列號與欄號！舉例來說，若想在整個表格範圍（而非單一欄）中找出 Jeff 的分數，只需在 A2 到 C11 的儲存格區域 中，透過 列號 6 與 欄號 3 即可快速定位他的分數，如下所示：

=INDEX(A2:C11,6,3)

然而，對於包含多列與多欄的大型資料庫而言，手動輸入確切的列號與欄號顯然相當不便——這正是結合使用 MATCH 函數的最佳時機。

如何在 Excel 中使用 MATCH 函數

Excel 中的 MATCH 函數會傳回指定項目在給定範圍中的位置（以數值表示）。其語法如下：

• lookup_value（必要）：指要在 lookup_array 中進行比對的值。
• lookup_array（必要）指您要 MATCH 函數搜尋的儲存格範圍。
• match_type（選用）：1、0 或 -1.
  • 1（預設值）：MATCH 會找出小於或等於 lookup_value 的最大值，且 lookup_array 中的值必須按升冪排序。
  • 0，MATCH 會精準找出第一個完全等於 lookup_value 的值。lookup_array 中的值可為任意順序。（當比對類型設為 0 時，還可使用萬用字元。）
  • －1時，MATCH 會找出大於或等於 lookup_value 的最小值，且 lookup_array 中的值必須按降冪排序。

例如，若要得知 名稱列表中 Vera 的位置，您可以這樣使用區分公式：

=MATCH("Vera",A2:A11,0)

√ 注意：結果「4」表示名字「Vera」位於清單中的第 4 個位置。

現在我們已經掌握了 Excel 中 INDEX 與 MATCH 函數的基本用法，接下來就捲起袖子，準備將這兩個強大函數結合運用吧！

如何在 Excel 中結合使用 INDEX 與 MATCH

請參閱下方範例，了解如何結合 INDEX 與 MATCH 函數：

若要找出 Evelyn 的分數，且已知考試分數位於第 3 欄，我們可 使用 MATCH 函數自動判斷列的位置，無需手動計數；接著再運用 INDEX 函數，輕鬆擷取 所識別列與第 3 欄交叉處的值：

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

由於這個公式看起來可能有點複雜，讓我們一步步拆解其中的每個部分。

此 INDEX 公式包含三個引數：

• row_num：MATCH("Evelyn",A2:A11,0)會傳回「Evelyn」在範圍 A2:A11 中的列位置，供 INDEX 使用，即5。
• column_num：3 指定 INDEX 在陣列中定位分數的第 3 欄。
• array：A2:C11 用於指定 INDEX 函數在特定列與欄的交集處傳回對應的值，範圍為 A2 到 C11. 最終，我們取得結果 90.

在上述公式中，我們使用了一個硬編碼值 「Evelyn」。然而在實際應用中，硬編碼值並不實用，因為每次查詢不同資料（例如另一位學生的分數）時，都必須手動修改公式。在這類情境下，我們可以透過儲存格參照建立動態公式。例如在此案例中，我會 將「Evelyn」改為 F2：

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

INDEX 與區分公式範例

在本節中，我們將探討如何根據不同需求，靈活運用 INDEX 與 MATCH 函數應對各種情境。

使用 INDEX 與 MATCH 執行雙向查詢

在先前的範例中，我們已知欄號，並運用區分公式找出列號。但如果連欄號也不確定呢？

在此情況下，我們可透過兩個 MATCH 函數執行雙向查詢（又稱矩陣查詢）：一個用來找出列號，另一個用來確定欄號。例如，若要查詢 Evelyn 的分數，請使用以下公式：

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

使用 INDEX 與 MATCH 執行向左查詢

現在，假設您需要查詢 Evelyn 所屬的班級。您可能已經發現，班級欄位位於姓名欄位的左側——這種情況已超出另一個強大的 Excel 查詢函數 VLOOKUP 的處理範圍。

事實上，能夠執行向左查詢正是 INDEX 與 MATCH 組合勝過 VLOOKUP 的一大優勢。

若要找出 Evelyn 的班級，請使用下列公式：在 B2:B11 中搜尋 Evelyn，並 從 A2:A11 中擷取對應的值。

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

使用 INDEX 與 MATCH 執行區分大小寫的查詢

MATCH 函數本身不區分大小寫。然而，當您需要公式能區分大小寫時，可透過加入 EXACT 函數來強化功能。將 MATCH 與 EXACT 結合至 INDEX 公式中，即可輕鬆實現區分大小寫的精準查詢，如下所示：

• array 是指您要從中傳回值的範圍。
• lookup_value 指的是要在 lookup_array 中進行比對的值（區分大小寫）。
• lookup_array 指的是您要提供給 MATCH 函數、用來與 lookup_value 進行比較的儲存格範圍。

例如，若要得知 JIMMY 的考試分數，請使用以下公式：

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ 注意：這是陣列公式，在 Excel 365、Excel 2021 及更新版本以外的版本中，皆需按下 Ctrl+Shift+Enter 才能輸入。

使用 INDEX 與 MATCH 尋找最接近的相符值

在 Excel 中，當您需要從資料集中找出最接近特定數值的項目時，結合使用 INDEX 與 MATCH 函數，並搭配 ABS 與 MIN 函數，將能輕鬆達成目標。

• array 是指您要從中傳回值的範圍。
• lookup_array 指的是您用來尋找與 lookup_value 最接近相符項目的值範圍。
• lookup_value 是指要尋找其最接近相符項目的值。

例如，若要找出 誰的分數最接近 85，請使用下列公式：在 C2:C11 中搜尋最接近 85 的分數，並 從 A2:A11 中擷取對應的值。

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ 注意：這是陣列公式，在 Excel 365、Excel 2021 及更新版本以外的版本中，皆需按下 Ctrl+Shift+Enter 才能正確輸入。

使用 INDEX 與 MATCH 執行多重條件查詢

若要找出符合多個條件的值（需跨兩欄或更多欄進行搜尋），請使用下列公式。此公式可讓您在不同欄位中指定多項條件，執行多重條件查詢，精準找出符合所有指定條件的目標值。

√ 注意：這是陣列公式，需按下 Ctrl+Shift+Enter 輸入。輸入後，編輯欄中將自動出現一對大括號。

• array 是指您要從中傳回值的範圍。
• (lookup_value=lookup_array)代表單一條件，用於檢查指定的 lookup_value 是否與 lookup_array 中的值相符。

例如，若要找出 A 班 Coco 的分數（其出生日期為 7/2/2008），您可以使用下列公式：

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

使用 INDEX 與 MATCH 在多個欄位中執行查詢

假設您面對的是包含多個資料欄位的表格。其中第一欄作為關鍵欄位，用以分類其他欄位中的資料。若要為特定項目判斷其所屬的類別或分類，您必須在資料欄位中進行搜尋，並將結果與參考欄位中的對應關鍵值建立關聯。

例如，在下方表格中，我們該如何使用 INDEX 與 MATCH 將學生 Shawn 與其所屬班級配對？雖然可以透過公式達成，但此公式相當冗長，不僅難以理解，更遑論記憶與輸入。

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

此時，Kutools for Excel 的索引並匹配多列功能便派上用場了！它能簡化整個流程，讓您快速輕鬆地將特定項目與對應類別配對。若要啟用這項強大工具，毫不費力地將 Shawn 與其班級配對，只需 下載並安裝 Kutools for Excel 增益集，並依照下列步驟操作：

1. 選取要顯示相符班級的目的儲存格。
2. 在 Kutools 選項卡上，點選公式助手＞ 查找和引用＞ 索引並匹配多列。
3. 
3. 在彈出的對話方塊中，執行下列操作：
   1. 按一下 Lookup_col 旁的第一個按鈕，即可選取包含您要傳回關鍵資訊（例如班級名稱）的欄位。（此處僅能選取單一欄。）
   2. 按一下 Table_rng 旁的第二個按鈕，即可選取要與所選 Lookup_col 中的值（例如學生姓名）進行比對的儲存格。
   3. 按一下 Lookup_value 旁的第三個按鈕，即可選取包含您欲比對班級之學生姓名（本例為 Shawn）的儲存格。
   4. 點擊確定。
   5. 

結果

Kutools 已自動產生公式，您會立即在目標儲存格中看到 Shawn 所屬的班級名稱。

使用 INDEX 與 MATCH 查找第一個非空白值

若要從欄或列中擷取第一個非空白值（忽略錯誤值），可使用基於 INDEX 與 MATCH 函數的公式。但若您不希望忽略範圍內的錯誤值，請加入 ISBLANK 函數。

• 取得欄或列中忽略錯誤的第一個非空白值：

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• 取得欄或列中包含錯誤的第一個非空白值：

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

使用 INDEX 與 MATCH 查找第一個數值

若要從欄或列中擷取第一個數值，請使用基於 INDEX、MATCH 與 ISNUMBER 函數的公式。

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

使用 INDEX 與 MATCH 查找最大值或最小值的關聯項目

若您需要在指定範圍內擷取與最大值或最小值相關的值，可搭配使用 MAX 或 MIN 函數與 INDEX 及 MATCH 函數。

• 使用 INDEX 與 MATCH 擷取與最大值相關聯的值：
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• 使用 INDEX 與 MATCH 擷取與最小值相關聯的值：
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• 上述公式中有兩個引數：
• • array 指的是您要從中提取相關資訊的範圍。
  • lookup_array 代表一組用來檢查或搜尋特定條件（例如最大值或最小值）的數值。

例如，若要找出誰的分數最高，請使用下列公式：

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

提示：自訂您的 #N/A 錯誤訊息

當您在 Excel 中使用 INDEX 與 MATCH 函數時，若找不到相符的結果，便可能出現 #N/A 錯誤。例如，在下方表格中，當您試圖查找名為 Samantha 的學生分數時，由於資料集中並無該使用者，系統就會顯示 #N/A 錯誤。

為提升試算表的使用者體驗，您可透過 IFNA 函數包裝您的 INDEX 區分公式，自訂此錯誤訊息：

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

以上即為 Excel 中 INDEX 與 MATCH 函數的完整教學內容，希望對您有所幫助！若您想探索更多 Excel 實用技巧，請點此處，立即瀏覽我們超過數千篇的精彩教學文章！