Skip to main content

立即探索 Kutools for Outlook 的全新免費版本！

Excel INDEX MATCH：基本和高級查找

Author: Amanda Li Last Modified: 2025-05-12

在 Excel 中，準確檢索特定數據通常是經常需要的。雖然 INDEX 和 MATCH 函數各有其優勢，但將它們結合起來可以解鎖一套強大的數據查找工具。它們一起提供了一系列的搜尋功能，從基本的水平和垂直查找到更高級的功能，如雙向、區分大小寫和多條件搜尋。與 VLOOKUP 相比，INDEX 和 MATCH 的組合提供了更廣泛的數據查找選項。在本教程中，讓我們深入探討它們可以共同實現的可能性。

如何在 Excel 中使用 INDEX 和 MATCH

在使用 INDEX 和 MATCH 函數之前，讓我們先確保了解 INDEX 和 MATCH 如何幫助我們查找值。

如何在 Excel 中使用 INDEX 函數

Excel 中的 INDEX 函數返回特定區域中給定位置的值。INDEX 函數的語法如下：

=INDEX(array, row_num, [column_num])
  • array（必需）指的是您希望返回值的區域。
  • row_num（必需，除非存在 column_num）指的是陣列的行號。
  • column_num（可選，但如果省略 row_num 則必需）指的是陣列的列號。

例如，要知道 Jeff 的分數，列表中的第6 位學生，您可以這樣使用 INDEX 函數：

=INDEX(C2:C11,6)

A screenshot of the result of the INDEX formula returning the score of the 6th student

√ 注意：範圍 C2:C11 是列出分數的地方，而數字 6 找到第 6 位學生的考試分數。

這裡讓我們做一個小測試。對於公式 =INDEX(A1:C1,2)，它會返回什麼值？--- 是的，它會返回出生日期，給定行中的第2個值。

現在我們應該知道，INDEX 函數可以完美地與水平或垂直區域一起工作。但如果我們需要它在具有多行和多列的更大區域中返回一個值呢？在這種情況下，我們應該同時應用行號和列號。例如，要在表格範圍內而不是單列中找到 Jeff 的分數，我們可以在 A2 到 C11 的單元格中使用行號6 和列號3來定位他的分數，如下所示：

=INDEX(A2:C11,6,3)

A screenshot of the result of the INDEX formula returning Jeff's score from a table range

我們應該了解的有關 Excel 中 INDEX 函數的事情：
  • INDEX 函數可以與垂直和水平區域一起工作。
  • 如果同時使用 row_num 和 column_num參數，row_num會在 column_num之前，INDEX會檢索指定 row_num 和 column_num交叉處的值。

然而，對於一個擁有多行和多列的真正大型數據庫，使用確切的行號和列號來應用公式對我們來說肯定不方便。這時我們應該結合使用 MATCH 函數。

如何在 Excel 中使用 MATCH 函數

Excel 中的 MATCH 函數返回一個數值，即給定區域中特定項目的位置。MATCH 函數的語法如下：

=MATCH(lookup_value, lookup_array, [match_type])
  • 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公式：

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

A screenshot showing the result of the MATCH formula returning the position of Vera in the list

√ 注意：結果“4”表示名稱“Vera”在列表中的第4 位。

我們應該了解的有關 Excel 中 MATCH 函數的事情：
  • MATCH 函數返回查找值在查找數組中的位置，而不是值本身。
  • MATCH 函數在重複項中返回第一個匹配項。
  • 就像 INDEX 函數一樣，MATCH 函數也可以與垂直和水平區域一起工作。
  • MATCH 不區分大小寫。
  • 如果 MATCH公式的 lookup_value 是文本形式，請將其用引號括起來。
  • 如果在 lookup_array 中找不到 lookup_value，則返回 #N/A 錯誤。

現在我們了解了 Excel 中 INDEX 和 MATCH 函數的基本用法，讓我們捲起袖子，準備結合這兩個函數。

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

請參見下面的示例，了解我們如何結合使用 INDEX 和 MATCH 函數：

要找到 Evelyn 的分數，知道考試分數在第3 列，我們可以使用 MATCH 函數自動確定行位置，而無需手動計算。隨後，我們可以使用 INDEX 函數檢索在識別的行和第3 列交叉處的值：

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

A screenshot showing the formula and the result for Evelyn's score

好吧，由於公式可能看起來有點複雜，讓我們逐一了解每個部分。

A screenshot showing the breakdown of the formula for combining INDEX and MATCH to find Evelyn's score

INDEX公式包含三個參數：

  • row_num: MATCH("Evelyn",A2:A11,0)提供 INDEX 在範圍 A2:A11 中值 "Evelyn" 的行位置，即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)

(AD) 使用 Kutools 簡化查找：無需輸入公式！

Kutools for Excel 的 高級查找 提供了 多種查找工具，專為滿足您的各種需求而設計。無論您是在執行多條件查找、跨多個工作表搜尋，還是一對多查找，高級查找 都能通過幾次點擊簡化過程。探索這些功能，看看 高級查找 如何改變您與 Excel 數據的互動方式。告別記住複雜公式的麻煩。

A screenshot of Kutools for Excel's Super Lookup tools in Excel ribbon

Kutools for Excel - 超過 300 種必備工具，讓 Excel 功能更強大。永久免費享受 AI 功能！立即獲取

INDEX 和 MATCH公式範例

在這部分，我們將討論使用 INDEX 和 MATCH 函數以滿足不同需求的不同情況。

INDEX 和 MATCH 應用雙向查找

在前面的示例中，我們知道列號並使用 MATCH公式找到行號。但如果我們也不確定列號呢？

在這種情況下，我們可以通過使用兩個 MATCH 函數進行雙向查找，也稱為矩陣查找：一個用於查找行號，另一個用於確定列號。例如，要知道 Evelyn 的分數，我們應該使用公式：

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

A screenshot showing a two-way lookup using INDEX and MATCH in Excel to find Evelyn's score

這個公式如何運作：
  • 第一個 MATCH公式在列表 A2:A11 中找到 Evelyn 的位置，提供5 作為 INDEX 的行號。
  • 第二個 MATCH公式確定分數的列，並返回3 作為 INDEX 的列號。
  • 公式簡化為 =INDEX(A2:C11,5,3)，INDEX 返回90。
INDEX 和 MATCH 應用左側查找

現在，讓我們考慮一個需要確定 Evelyn 班級的情況。您可能已經注意到，班級列位於名稱列的左側，這超出了另一個強大的 Excel 查找函數 VLOOKUP 的能力。

事實上，執行左側查找的能力是 INDEX 和 MATCH 的組合優於 VLOOKUP 的一個方面。

要找到 Evelyn 的班級，使用以下公式在 B2:B11 中搜尋 Evelyn，並從 A2:A11 中檢索相應的值。

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

A screenshot showing how to use INDEX and MATCH to find Evelyn's class from a left-side lookup in Excel

注意：您可以輕鬆地使用 Kutools for Excel 的 從右到左查找 功能，通過幾次點擊來執行特定值的左側查找。要實施此功能，請導航到 Excel 中的 Kutools 標籤，然後在 公式 組中點擊 高級查找 > 從右到左查找。

A screenshot of the LOOKUP from Right to Left feature in Kutools for Excel

Kutools for Excel - 超過 300 種必備工具，讓 Excel 功能更強大。永久免費享受 AI 功能！立即獲取

INDEX 和 MATCH 應用區分大小寫的查找

MATCH 函數本質上不區分大小寫。然而，當您需要公式區分大小寫時，可以通過結合 EXACT 函數來增強它。通過在 INDEX公式中將 MATCH 函數與 EXACT 結合，您可以有效地執行區分大小寫的查找，如下所示：

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array),0))
  • array 指的是您希望返回值的區域。
  • lookup_value 指的是要匹配的值，考慮字符的大小寫，在 lookup_array 中。
  • lookup_array 指的是您希望 MATCH 與 lookup_value 比較的單元格範圍。

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

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

√ 注意：這是一個陣列公式，您需要使用 Ctrl + Shift + Enter 鍵輸入，除了在 Excel365、Excel2021 和更新版本中。

A screenshot showing how to use INDEX and MATCH with EXACT for a case-sensitive lookup in Excel

這個公式如何運作：
  • EXACT 函數將 "JIMMY" 與列表 A2:A11 中的值進行比較，考慮字符的大小寫：如果兩個字串精確匹配，考慮到大小寫字符，EXACT 返回 TRUE；否則返回 FALSE。結果，我們得到一個包含 TRUE 和 FALSE 值的陣列。
  • MATCH 函數然後檢索陣列中第一個 TRUE 值的位置，應該是10。
  • 最後，INDEX 檢索由 MATCH 在陣列中提供的第10 個位置的值。

注意：

  • 記得正確輸入公式，按 Ctrl + Shift + Enter，除非您使用的是 Excel365、Excel2021 或 更新版本，在這種情況下，只需按 Enter。
  • 上述公式在單個列表 C2:C11 中搜尋。如果您想在具有多列和多行的範圍內搜尋，例如 A2:C11，您應該為 INDEX 提供列號和行號：
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • 在這個修訂的公式中，我們使用 MATCH 函數在範圍 A2:A11 中搜尋 "JIMMY"，考慮字符的大小寫，一旦找到匹配，我們從範圍 A2:C11 的第3 列中檢索相應的值。
INDEX 和 MATCH 查找最接近的匹配

在 Excel 中，您可能會遇到需要在數據集中找到特定值的最近或最接近匹配的情況。在這種情況下，使用 INDEX 和 MATCH 函數的組合，結合 ABS 和 MIN 函數，會非常有幫助。

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • 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))

√ 注意：這是一個陣列公式，您需要使用 Ctrl + Shift + Enter 鍵輸入，除了在 Excel365、Excel2021 和更新版本中。

A screenshot demonstrating how to use INDEX and MATCH with ABS and MIN functions to find the closest match in Excel

這個公式如何運作：
  • ABS(C2:C11-85) 計算範圍 C2:C11 中每個值與85 的絕對差，生成一個絕對差的陣列。
  • MIN(ABS(C2:C11-85)) 找到絕對差陣列中的最小值，代表與85 的最接近差異。
  • MATCH 函數 MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) 然後找到絕對差陣列中最小絕對差的位置，應該是10。
  • 最後，INDEX 檢索在列表 A2:A11 中與範圍 C2:C11 中最接近85 的分數相對應的位置的值。

注意：

  • 記得正確輸入公式，按 Ctrl + Shift + Enter，除非您使用的是 Excel365、Excel2021 或 更新版本，在這種情況下，只需按 Enter。
  • 在平局的情況下，這個公式將返回第一個匹配。
  • 要找到與平均分數最接近的匹配，將公式中的85 替換為 AVERAGE(C2:C11)。
INDEX 和 MATCH 應用多條件查找

要找到滿足多個條件的值，需要您在兩列或多列中搜尋，請使用以下公式。該公式允許您通過在不同列中指定各種條件來執行多條件查找，幫助您找到滿足所有指定條件的所需值。

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…),0))

√ 注意：這是一個陣列公式，您需要使用 Ctrl + Shift + Enter 鍵輸入。然後在公式欄中會顯示一對大括號。

  • array 指的是您希望返回值的區域。
  • (lookup_value=lookup_array)代表一個單一條件。此條件檢查特定的lookup_value是否與lookup_array中的值匹配。

例如，要找到班級 A 的 Coco 的分數，其出生日期為2008 年7 月2 日，您可以使用以下公式：

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

A screenshot demonstrating the use of INDEX and MATCH for multi-criteria lookup in Excel

注意：

  • 在此公式中，我們避免硬編碼值，這樣可以通過修改 G2、G3 和 G4 單元格中的值輕鬆獲得不同信息的分數。
  • 您應該按 Ctrl + Shift + Enter 輸入公式，除非在 Excel365, Excel2021 或 更新版本中，在這種情況下，您可以簡單地按 Enter.
    如果您經常忘記使用 Ctrl + Shift + Enter 來完成公式並獲得錯誤的結果，請使用以下稍微複雜的公式，您可以簡單地按 Enter 鍵： 
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • 公式可能很複雜且難以記住。要簡化多條件查找而無需手動輸入公式，請考慮使用 Kutools for Excel的 多條件查找 功能。一旦安裝了 Kutools，請導航到 Kutools 標籤在您的 Excel 中，然後點擊 高級查找 > 多條件查找 在 公式 組中。

    A screenshot of the Multi-condition Lookup feature in Kutools for Excel

    Kutools for Excel - 超過 300 種必備工具，讓 Excel 功能更強大。永久免費享受 AI 功能！立即獲取

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)), "")

A screenshot of the formula used to apply a lookup across multiple columns

這就是 Kutools for Excel 的 索引並匹配多列 功能的用武之地。它簡化了過程，使特定條目與其相應類別的匹配變得快速而簡單。要解鎖這個強大的工具並輕鬆地將 Shawn 與他的班級匹配，只需 下載並安裝 Kutools for Excel 插件，然後按以下步驟操作：

  1. 選擇您希望顯示匹配班級的目標單元格。
  2. 在 Kutools 標籤上，點擊公式助手 > 查找與引用 > 索引並匹配多列。
  3. A screenshot of the Index and Match on Multiple Columns option on the Kutools tab in Excel
  4. 在彈出對話框中，按以下步驟操作：
    1. 點擊第1 個 A screenshot of the range selection button in the Fomrulas Helper dialog 按鈕旁邊 查找列 以選擇包含您希望返回的關鍵信息的列，即班級名稱。（此處您只能選擇單列。）
    2. 點擊第2 個 A screenshot of the range selection button in the Fomrulas Helper dialog 按鈕旁邊 表格區域 以選擇單元格以匹配所選 查找列中的值，即學生姓名。
    3. 點擊第3 個 A screenshot of the range selection button in the Fomrulas Helper dialog 按鈕旁邊 查找值 以選擇包含您希望與其班級匹配的學生姓名的單元格，在這種情況下是 Shawn。
    4. 點擊 OK。
    5. A screenshot of the Fomrulas Helper dialog

結果

Kutools 已自動生成公式，您將立即在目標單元格中看到 Shawn 的班級名稱。

A screenshot of the formula generated by Kutools locating Shawn's class name from a table

注意：要嘗試索引並匹配多列功能，您需要在計算機上安裝 Kutools for Excel。如果您尚未安裝，請不要等待 --- 現在就下載並安裝。讓 Excel 更加智能化！

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))

    A screenshot of the INDEX MATCH formulas used to lookup for first non-blank value

注意：

  • 以上是需要您使用 Ctrl + Shift + Enter 鍵輸入的陣列公式，除了在 Excel365、Excel2021 和更新版本中。
  • 查看本教程以獲取詳細說明：在列或行中獲取第一個非空值。
INDEX 和 MATCH 查找第一個數值

要從列或行中檢索第一個數值，請使用基於 INDEX、MATCH 和 ISNUMBER 函數的公式。

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

A screenshot of the INDEX MATCH formulas used to lookup for the first numeric value

注意：

  • 這是一個需要您使用 Ctrl + Shift + Enter 鍵輸入的陣列公式，除了在 Excel365、Excel2021 和更新版本中。
  • 查看本教程以獲取詳細說明：在列或行中獲取第一個數值。
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))

A screenshot of the INDEX MATCH formula used to lookup for MAX associations

這個公式如何運作：
  • MAX(C2:C11) 搜索範圍 C2:C11 中的最高值，即96。
  • MATCH 函數然後找到陣列 C2:C11 中最高值的位置，應該是1。
  • 最後，INDEX 檢索列表 A2:A11 中的第1 個值。

注意：

  • 在多個最大值或最小值的情況下，如上例中兩個學生獲得相同的最高分，這個公式將返回第一個匹配。
  • 要確定誰的分數最低，請使用以下公式： 
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))
提示：自定義您的 #N/A 錯誤消息

在使用 Excel 的 INDEX 和 MATCH 函數時，如果沒有匹配結果，您可能會遇到 #N/A 錯誤。例如，在下表中，當嘗試查找名為 Samantha 的學生的分數時，由於她不在數據集中，出現了 #N/A 錯誤。

A screenshot of the #N/A error result returned by an INDEX MATCH formula

為了使您的電子表格更具用戶友好性，您可以通過將您的 INDEX MATCH公式包裹在 IFNA 函數中來自定義此錯誤消息：

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

A screenshot of the #N/A error replaced with a tailored message using INDEX and MATCH

注意：

  • 您可以通過將 "Not found" 替換為您選擇的任何文本來自定義錯誤消息。
  • 如果您想處理所有錯誤，而不僅僅是 #N/A，請考慮使用 IFERROR 函數而不是 IFNA: 
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    請注意，抑制所有錯誤可能不明智，因為它們作為潛在問題的警報。

以上是所有與 Excel 中 INDEX 和 MATCH 函數相關的內容。希望您覺得本教程有幫助。如果您想探索更多 Excel 技巧和竅門，請點擊此處訪問我們豐富的數千個教程集合。