跳到主要內容
 

Excel INDEX MATCH:基本和進階查找

作者:阿曼達李 最後修改時間:2023-12-29

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


如何在 Excel 中使用 INDEX 和 MATCH

在我們使用 INDEX 和 MATCH 函數之前,讓我們確保我們知道 INDEX 和 MATCH 如何幫助我們首先查找值。


如何在Excel中使用INDEX函數

INDEX Excel 中的函數返回特定範圍內給定位置的值。 INDEX 函數的語法如下:

=INDEX(array, row_num, [column_num])
  • 排列 (required) 是指您要從中返回值的範圍。
  • 行數 (必需,除非 列數 存在)指的是數組的行號。
  • 列數 (可選,但如果 行數 被省略)指的是數組的列號。

例如,要知道 傑夫的分數是, 6清單中的第一個學生,您可以像這樣使用 INDEX 函數:

=INDEX(C2:C11,6)

excel索引匹配01

√ 注:範圍 C2:C11 是列出分數的地方,而數字 6 找到考試成績 6第一個學生。

下面我們來做一個小測試。 對於公式 =索引(A1:C1,2),它會回傳什麼值? --- 是的,它會返回 出生日期是, 2給定行中的 nd 值。

現在我們應該知道 INDEX 函數可以完美地處理水平或垂直範圍。 但是如果我們需要它返回一個包含多個行和列的更大範圍內的值怎麼辦? 那麼,在這種情況下,我們應該同時應用行號和列號。 例如,要找出 傑夫的分數 在表格範圍內而不是單一列中,我們可以透過 行數 6列數 3 ,詳見 從 A2 到 C11 的細胞 喜歡這個:

=INDEX(A2:C11,6,3)

excel索引匹配02

關於 Excel 中的 INDEX 函數我們應該要了解的事情:
  • INDEX 函數可以處理垂直和水平範圍。
  • 如果兩者都 行數 列數 使用參數, 行數 領先於 列數,並且 INDEX 會檢索指定的交集處的值 行數 列數.

但是,對於一個非常大的多行多列的數據庫,我們應用精確的行號和列號的公式肯定不方便。 而這正是我們應該結合使用 MATCH 函數的時候。


如何在Excel中使用MATCH函數

Excel 中的 MATCH 函數返回一個數值,即給定範圍內特定項目的位置。 MATCH 函數的語法如下:

=MATCH(lookup_value, lookup_array, [match_type])
  • Lookup_Array中 (必需)指的是要匹配的值 查找數組.
  • 查找數組 (必填)是指您希望 MATCH 搜索的單元格範圍。
  • 比賽類型 (可選的): 1, 0 or -1.
    • 1 (默認),MATCH 將找到小於或等於 Lookup_Array中. 中的值 查找數組 必須按升序排列。
    • 0, MATCH 將找到第一個完全等於 Lookup_Array中. 中的值 查找數組 可以按任何順序排列。 (對於匹配類型設置為0的情況,可以使用通配符。)
    • -1, MATCH 將找到大於或等於 Lookup_Array中. 中的值 查找數組 必須按降序排列。

例如,要知道 Vera 在名單中的位置,您可以像這樣使用 MATCH 公式:

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

excel索引匹配3

√ 註:結果「4」表示名稱「Vera」位於清單的第 4 位。

關於 Excel 中的 MATCH 函數我們應該了解的事情:
  • MATCH 函數返回查找值在查找數組中的位置,而不是值本身。
  • MATCH 函數在重複的情況下返回第一個匹配項。
  • 就像 INDEX 函數一樣,MATCH 函數也可以處理垂直和水平範圍。
  • MATCH 不區分大小寫。
  • 如果 Lookup_Array中 MATCH 公式的內容是文字形式,請將其以引號引起來。
  • 如果 Lookup_Array中 中沒有找到 查找數組是, #N / A 回傳錯誤。

現在我們已經了解了Excel中INDEX和MATCH函數的基本用法,讓我們擼起袖子準備將這兩個函數結合起來。


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

請參閱下面的示例以了解我們如何組合 INDEX 和 MATCH 函數:

伊芙琳的分數,知道考試成績在 3rd 欄,我們可以 使用MATCH函數自動確定行位置 無需手動計數。 隨後,我們可以使用 INDEX 函數來檢索 所辨識的行和第三列的交叉點處的值:

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

excel索引匹配4

好吧,由於公式可能看起來有點複雜,讓我們逐一討論。

excel索引匹配5

INDEX 公式包含三個參數:

  • 行數: MATCH("伊芙琳",A2:A11,0) 為 INDEX 提供值“的行位置”伊夫林「 在範圍中 A2:A11,這是 5.
  • 列數: 3 指定 3INDEX 的 rd 欄位用於在陣列中尋找分數。
  • 排列: A2:C11 指示 INDEX 傳回指定行和列交集處的符合值,範圍為 A2至C11。 最後我們得到結果 90.

在上面的公式中,我們使用了一個硬編碼值, “伊芙琳”。 然而,在實踐中,硬編碼值是不切實際的,因為每次我們尋求搜尋不同的資料(例如另一個學生的分數)時都需要修改它們。 在這種情況下,我們可以利用儲存格參考來建立動態公式。 例如,在這種情況下,我將 將“伊芙琳”更改為 F2:

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

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

Excel的Kutools's 超級查詢 提供 多種查找工具 量身定制,滿足您的各種需求。 無論您是執行多條件查找、跨多個工作表搜尋還是進行一對多查找, 超級查詢 只需點擊幾下即可簡化流程。 探索這些功能 看怎麼樣 超級查詢 改變您與 Excel 資料互動的方式。 告別記住複雜公式的麻煩。

Kutools 尋找工具

Excel的Kutools - 為您提供 300 多種便利功能,輕鬆提高工作效率。 不要錯過 30 天全功能免費試用的機會! 現在就開始!


INDEX 和 MATCH 公式範例

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


INDEX 和 MATCH 應用雙向查找

在前面的範例中,我們知道列號並使用 MATCH 公式來尋找行號。 但是如果我們也不確定列號怎麼辦?

在這種情況下,我們可以使用兩個 MATCH 函數執行雙向查找(也稱為矩陣查找):一個用於尋找行號,另一個用於確定列號。 例如,要知道 伊芙琳的分數,我們應該使用公式:

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

excel索引匹配6

這個公式是如何運作的:
  • 第一個 MATCH 公式找出 Evelyn 在清單 A2:A11 中的位置,提供 5 作為 INDEX 的行號。
  • 第二個 MATCH 公式決定分數列並傳回 3 作為 INDEX 的列號。
  • 此公式簡化為 =索引(A2:C11,5,3),並且 INDEX 返回 90.

INDEX 和 MATCH 應用左查找

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

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

伊芙琳的課堂,採用以下公式 在 B2:B11 中尋找伊芙琳 從A2:A11檢索對應的值.

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

excel索引匹配7

注意: 您可以使用以下命令輕鬆執行特定值的左查找 從右到左查找 的特點 Excel的Kutools 只需點擊幾下。 若要實現該功能,請導航至 庫工具 Excel 中的選項卡,然後按一下 超級查詢 > 從右到左查找 ,詳見 公式 組。

從右到左查找

如果您還沒有安裝Kutools,請點擊這裡 下載並獲得 30 天全功能免費試用!


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

MATCH 函數本質上不區分大小寫。 然而,當您需要公式區分大小寫字元時,您可以透過合併來增強它 精確 功能。 透過在 INDEX 公式中將 MATCH 函數與 EXACT 結合起來,您可以有效地執行區分大小寫的查找,如下所示:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • 排列 指的是您想要傳回值的範圍。
  • Lookup_Array中 指的是要匹配的值,考慮到字元的大小寫,在 查找數組.
  • 查找數組 指的是與 MATCH 進行比較的單元格範圍 Lookup_Array中.

例如,要知道 吉米的考試成績,使用以下公式:

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

√ 注意:這是一個數組公式,需要輸入 按Ctrl + 轉移 + Enter,Excel 365 和 Excel 2021 中除外。

excel索引匹配8

這個公式是如何運作的:
  • EXACT 函數比較 “吉米” 與清單中的值 A2:A11,考慮字符的大小寫:如果兩個字串精確匹配,同時考慮大寫和小寫字符,則 EXACT 返回 TRUE; 否則,它返回 。 結果,我們得到 包含 TRUE 和 FALSE 值的陣列.
  • MATCH 函數然後檢索 第一個 TRUE 值的位置 在數組中,應該是 10.
  • 最後,INDEX 檢索位於 10MATCH 在陣列中提供的第一個位置。

注意:

  • 請記得按正確輸入公式 Ctrl + Shift + Enter,除非你正在使用 Excel中365 or Excel中2021,在這種情況下,只需按 Enter.
  • 上述公式在單一清單中搜尋 C2:C11。 如果您想在具有多列和行的範圍內進行搜索,請說 A2:C11,您應該向 INDEX 提供列號和行號:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • 在這個修改後的公式中,我們使用 MATCH 函數在範圍內搜尋“JIMMY”,考慮字元的大小寫 A2:A11,一旦找到匹配項,我們就會從 3範圍的第 rd 列 A2:C11.

INDEX 和 MATCH 尋找最接近的匹配項

在 Excel 中,您可能會遇到需要在資料集中尋找與特定值最接近或最接近的匹配項的情況。 在這種情況下,結合使用 INDEX 和 MATCH 函數以及 ABS 和 MIN 函數會非常有用。

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • 排列 指的是您想要傳回值的範圍。
  • 查找數組 指的是您想要找到最接近匹配的值的範圍 Lookup_Array中.
  • Lookup_Array中 指的是找到最接近匹配的值。

例如,要找出 誰的分數最接近85,使用以下公式 搜尋 C85:C2 中最接近 11 分的分數 從A2:A11檢索對應的值.

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

√ 注意:這是一個數組公式,需要輸入 按Ctrl + 轉移 + Enter,Excel 365 和 Excel 2021 中除外。

這個公式是如何運作的:
  • ABS(C2:C11-85) 計算範圍內每個值之間的絕對差 C2:C11 85,產生絕對差異數組。
  • 最小值(ABS(C2:C11-85)) 找出絕對差值數組中的最小值,它表示與 85 最接近的差值。
  • 匹配功能 MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) 然後在絕對差數組中找到最小絕對差的位置,它應該是 10.
  • 最後,INDEX 檢索清單中該位置的值 A2:A11 對應最接近的分數 85 範圍中 C2:C11.

注意:

  • 請記得按正確輸入公式 Ctrl + Shift + Enter,除非你正在使用 Excel中365 or Excel中2021,在這種情況下,只需按 Enter.
  • 如果出現平局,此公式將傳回第一個符合項。
  • 最接近平均分數的匹配,替換 85 在公式中 平均值(C2:C11).

INDEX 和 MATCH 以應用具有多個條件的查找

若要尋找符合多個條件的值(需要跨兩列或多列進行搜尋),請使用下列公式。 此公式可讓您透過在不同列中指定各種條件來執行多條件查找,協助您找到滿足所有指定條件的所需值。

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

√ 注意:這是一個數組公式,需要輸入 按Ctrl + 轉移 + Enter. 一對大括號將顯示在公式欄中。

  • 排列 指的是您想要傳回值的範圍。
  • (查找值=查找數組) 代表單一條件。 此條件檢查特定的 Lookup_Array中 匹配中的值 查找數組.

例如,要找到 A班Coco的成績,出生日期為7/2/2008,您可以使用以下公式:

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

excel索引匹配9

注意:

  • 在這個公式中,我們避免了對值進行硬編碼,從而可以透過修改單元格中的值來簡單地獲得具有不同資訊的分數 G2, G3G4.
  • 您應該透過按輸入公式 Ctrl + Shift + Enter 除了在 Excel中365 or Excel中2021,您只需按 Enter.
    如果你經常忘記使用 Ctrl + Shift + Enter 要完成公式並得到不正確的結果,請使用以下稍微複雜的公式,您可以用一個簡單的公式來完成 Enter 鍵:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • 這些公式可能很複雜且難以記住。 若要簡化多條件查找而不需要手動輸入公式,請考慮使用 Excel的Kutools多條件查找 特徵。 安裝 Kutools 後,導覽到 庫工具 Excel 中的選項卡,然後按一下 超級查詢 > 多條件查找 ,詳見 公式 組。

    多條件查找

    如果您還沒有安裝Kutools,請點擊這裡 下載並獲得 30 天全功能免費試用!


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

那邊 Excel的Kutools's 多列上的索引和匹配 功能派上用場。 它簡化了流程,可以快速輕鬆地將特定條目與其相應的類別進行配對。 要解鎖這個強大的工具並輕鬆地將 Shawn 與他的班級匹配起來,只需 下載並安裝 Kutools for Excel 插件,然後執行以下操作:

  1. 選擇要顯示符合類別的目標儲存格。
  2. 庫工具 標籤,點擊 公式助手 > 查找和參考 > 多列上的索引和匹配.
  3. excel索引匹配11
  4. 在彈出的對話框中進行如下操作:
    1. 按一下第 1 個 excel索引匹配圖標 按鈕旁邊 查找列 選擇包含要傳回的關鍵資訊(即類別名稱)的欄位。 (此處只能選擇單一列。)
    2. 點選第2個 excel索引匹配圖標 按鈕旁邊 表_rng 選擇單元格以匹配所選中的值 查找列,即學生姓名。
    3. 點選第三個 excel索引匹配圖標 按鈕旁邊 查找值 選擇包含要與班級相符的學生姓名的儲存格,在本例中為 Shawn。
    4. 點擊 OK.
    5. excel索引匹配12

結果

Kutools 已自動產生公式,您將看到 Shawn 的班級名稱立即顯示在目標儲存格中。

注意: 要試用 多列上的索引和匹配 功能,您需要在電腦上安裝 Kutools for Excel。如果您還沒有安裝,請不要等待--- 立即下載並安裝,享有 30 天免費試用,無任何限制。 今天就讓 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))

注意:


INDEX 和 MATCH 用來找出第一個數值

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

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

注意:

  • 這是一個陣列公式,需要您輸入 按Ctrl + 轉移 + Enter,Excel 365 和 Excel 2021 中除外。
  • 查看本教學以取得詳細說明: 獲取列或行中的第一個數值.

INDEX 和 MATCH 用於尋找 MAX 或 MIN 關聯

如果需要檢索與某個範圍內的最大值或最小值關聯的值,可以將 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))
  • 上面的公式有兩個參數:
    • 排列 指的是要傳回相關資訊的範圍。
    • 查找數組 表示要檢查或搜尋特定標準的一組值,即最大值或最小值。

例如,如果您想確定 誰得分最高,採用以下公式:

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

這個公式是如何運作的:
  • 最大(C2:C11) 搜尋範圍內的最高值 C2:C11,這是 96.
  • MATCH 函數接著尋找陣列中最高值的位置 C2:C11,應該是 1.
  • 最後,INDEX 檢索 1清單中的 st 值 A2:A11.

注意:

  • 如果有多個最大值或最小值,如上例所示,其中兩個學生獲得相同的最高分,則此公式將傳回第一個符合項。
  • 要確定誰的得分最低,請使用以下公式:
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

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

使用 Excel 的 INDEX 和 MATCH 函數時,如果沒有符合結果,您可能會遇到 #N/A 錯誤。 例如,在下表中,當嘗試尋找名為 Samantha 的學生的分數時,會出現 #N/A 錯誤,因為資料集中不存在她。

excel索引匹配15

為了使電子表格更加用戶友好,您可以透過將 INDEX MATCH 公式包裝在 IFNA 函數中來自訂此錯誤訊息:

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

excel索引匹配16

注意:

  • 您可以透過替換來自訂錯誤訊息 “未找到” 與您選擇的任何文字。
  • 如果您想處理所有錯誤,而不僅僅是#N/A,請考慮使用 錯誤 函數而不是 干擾素:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    請注意,可能不建議抑制所有錯誤,因為它們會作為公式中潛在問題的警報。

以上就是Excel中INDEX和MATCH函數相關的全部相關內容。 我希望本教程對您有所幫助。 如果您想探索更多 Excel 提示和技巧, 請點擊這裡 訪問我們廣泛收集的超過數千個教程。