如何在 Excel 中使用 INDEX MATCH
INDEX MATCH 是一組用來在表格中查找數值的函數。MATCH 找出你要查找內容的位置,INDEX 則從另一欄傳回該位置的儲存格。這組函數在所有版本的 Excel 中都能運作,可以向左查找,而且在插入欄位後仍能正常運作。
以下包括基本模式、向左查找和雙向查找。結尾的課程可以讓你練習。
1. 基本模式
模式為 =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))。最後的 0 表示完全符合,這幾乎是你每次都需要的設定。兩個範圍必須涵蓋相同的列,而傳回範圍則是你要取得答案的那一欄。
範例:墨水的價格
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | 名稱 | 價格 | 查詢 | 價格 | |
| 2 | 101 | 鋼筆 | 2.5 | 墨水 | 7 | |
| 3 | 102 | 墨水 | 7 | |||
| 4 | 103 | 便條紙 | 4 |
MATCH 在 B2:B4 中找到墨水,位於第二個位置,而 INDEX 傳回 C2:C4 中的第二個儲存格,即 7。
2. 向左查找
VLOOKUP 只能傳回查閱欄右側的欄位。使用 INDEX MATCH,你只需將傳回範圍指向任何欄位,即使是位於查閱欄左側的欄位。
範例:墨水的編號
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | 名稱 | 價格 | 查詢 | ID | |
| 2 | 101 | 鋼筆 | 2.5 | 墨水 | 102 | |
| 3 | 102 | 墨水 | 7 | |||
| 4 | 103 | 便條紙 | 4 |
編號欄位位於名稱欄的左側。INDEX 不受影響,會傳回 102。
3. 雙向查找
要找出列與欄交會處的數值,請把整個表格交給 INDEX,並使用兩個 MATCH 函數:第一個找列,第二個找欄。
範例:二月的墨水
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 產品 | 一月 | 二月 | 三月 | 結果 | |
| 2 | 鋼筆 | 5 | 6 | 7 | 9 | |
| 3 | 墨水 | 8 | 9 | 10 |
墨水是 B2:D3 的第二列,二月是第二欄,所以 INDEX 傳回 9。
4. INDEX MATCH 或 XLOOKUP
在 Microsoft 365 和 Excel 2021 或更新版本中,XLOOKUP 能用一個函數完成相同的工作,而且更容易閱讀。INDEX MATCH 仍值得學習,因為它適用於較舊的檔案、Google 試算表,以及你將接手的活頁簿。請參閱 VLOOKUP 與 XLOOKUP 指南中的比較。
常見錯誤
省略最後的 0。 沒有最後一個參數時,MATCH 會假設清單已排序,可能傳回錯誤的列。
修正: MATCH 結尾一定要加上 0,表示完全符合。
範圍從不同列開始。 若一個範圍從第 2 列開始,另一個從第 3 列開始,每個結果都會偏移一列。
修正: 選取兩個範圍時,讓起始列和結束列都相同。
出現 #N/A 結果。 查閱值不在清單中,常見原因是多了空格,或數字被儲存為文字。
修正: 用 TRIM 清理資料,或把公式包在 IFERROR 中以顯示訊息。
常見問題
INDEX MATCH 比 VLOOKUP 快嗎?
在大型表格上可能較快,因為它只讀取兩欄。對於日常工作表,差別並不大,所以請選擇你較容易閱讀的方法。
INDEX MATCH 可以傳回多個欄位嗎?
將 INDEX 的傳回範圍設為多欄,並以 0 作為欄號;或在 Microsoft 365 中使用 XLOOKUP,它可以直接傳回多欄。
MATCH 中的 0 是什麼意思?
它是比對類型。0 表示完全符合;1 表示在已排序的清單中,找出不大於查閱值的最大值;-1 表示在遞減清單中,找出不小於查閱值的最小值。
相關函數
- INDEXINDEX 會傳回範圍中指定列與欄的值。搭配 MATCH 就能變成彈性的查詢。包含語法、範例,以及 INDEX/MATCH 的說明。.
- MATCHMATCH 會傳回值在清單中的位置,例如「蓮娜」是第 3 個名字。包含語法、比對類型、範例,以及搭配 INDEX 的雙向查詢。.
- VLOOKUPVLOOKUP 會在表格的第一欄中找到一個值,並從同一列的另一欄傳回值。包含語法、範例、常見錯誤與練習。.
- XLOOKUPXLOOKUP 會在一欄或一列中搜尋,並從另一欄傳回對應的值,可以朝任何方向查詢,並內建「找不到」的訊息。包含語法、範例與練習。.
- XMATCHXMATCH 會傳回某個值在清單中的位置,預設為完全符合,也支援大約符合,並可從最後一項開始搜尋。包含語法與範例。.
更多關於 INDEX 與 MATCH
本指南說明這組公式的寫法,並示範兩個查閱範例。這篇補充的是:為什麼這組公式在欄位變動後仍然可靠、如何以兩個條件進行查閱,以及如何讀懂別人寫的 INDEX MATCH 公式。
插入欄位後仍然正確的原因
VLOOKUP 要求的是欄號,例如 3。如果有人在表格中插入一欄,這個號碼就錯了,公式會悄悄傳回旁邊那一欄的值。INDEX MATCH 指向的是整個範圍,而 Excel 在插入欄位時會移動範圍,因此公式仍會傳回正確的欄位。
=INDEX(C2:C100,MATCH(F2,B2:B100,0))以兩個條件查閱
要依兩個值找出某一列,例如地區和產品,請讓 MATCH 搜尋一個組合鍵。在 MATCH 中以 & 連接兩欄,並以同樣方式連接兩個查閱儲存格。在較舊版本的 Excel 中,這必須以陣列公式輸入,也就是按 Ctrl+Shift+Enter。在 Microsoft 365 中,它是一般公式即可使用。
=INDEX(C2:C100,MATCH(F1&F2,A2:A100&B2:B100,0))如果表格很大,公式使工作表變慢,可以新增一個輔助欄,只把兩個鍵合併一次,然後比對這一欄。
讀懂 INDEX MATCH 公式
由內往外讀。先看 MATCH:它在找什麼、在哪個範圍中找,而且最後的參數是否為 0?然後看 INDEX:答案來自哪個範圍,而且它的起始列是否與查閱範圍相同?大多數出錯的公式,都是因為兩個範圍從不同的列開始。
如果你使用 Microsoft 365,可以把同一個查閱改用 XLOOKUP 比較,並閱讀[VLOOKUP 與 XLOOKUP 的差異](/guides/vlookup-vs-xlookup)。若要練習這組函數本身,請開啟[INDEX MATCH 課程](/learn/index-match)。