VLOOKUP 與 XLOOKUP:該用哪一個?
兩個函數都是在一欄中找出某個值,並傳回同一列中對應的值。XLOOKUP 是較新的函數,修正了 VLOOKUP 的大部分限制,但 VLOOKUP 在既有的活頁簿中仍然到處可見。
本指南說明真正重要的差異,以及一個簡單的選擇方法。
VLOOKUP:從第一欄查找,計算欄號
VLOOKUP 一律搜尋你指定的表格中的第一欄,並傳回你數出的欄號所對應的值。最後一個引數應為 FALSE,表示完全符合;若省略,則會變成大約符合,需要已排序的資料。
範例:使用 VLOOKUP 依產品編號查詢價格
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 產品編號 | 產品 | 價格 | 庫存 | 產品編號 | P-102 | |
| 2 | P-101 | 桌燈 | 24.5 | 40 | 價格 | 149 | |
| 3 | P-102 | 辦公椅 | 149 | 12 | |||
| 4 | P-103 | 螢幕架 | 39.9 | 25 | |||
| 5 | P-104 | 鍵盤 | 59 | 0 |
價格位於 A2:D5 的第三欄。如果有人在表格內插入一欄,那個 3 就可能指向錯誤的資料。
XLOOKUP:任意方向,不需要欄號
XLOOKUP 會把要搜尋的欄與要傳回的欄分別當作兩個範圍。這兩個範圍可以位於任何位置,因此能向左查找,插入欄位也不會讓公式出錯。預設為完全符合。
範例:使用 XLOOKUP 向左查找
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 產品編號 | 產品 | 價格 | 庫存 | 鍵盤的編號 | |
| 2 | P-101 | 桌燈 | 24.5 | 40 | P-104 | |
| 3 | P-102 | 辦公椅 | 149 | 12 | ||
| 4 | P-103 | 螢幕架 | 39.9 | 25 | ||
| 5 | P-104 | 鍵盤 | 59 | 0 |
編號位於 A 欄,在名稱 B 欄的左邊。VLOOKUP 無法傳回位於搜尋欄左邊的欄。
其他差異
缺少值: VLOOKUP 會傳回 #N/A,因此你需要用 IFNA 或 IFERROR 把它包起來。XLOOKUP 內建第 4 個引數,可以直接指定找不到時的訊息。
搜尋方向: XLOOKUP 可以從最後一個項目開始搜尋(搜尋模式 -1),適合用來找最新的一筆資料。VLOOKUP 只會找到第一個符合的值。
大約符合: VLOOKUP 需要遞增排序的資料。XLOOKUP 的比對模式 -1 和 1 則可以用在未排序的資料上。
版本: XLOOKUP 存在於 Excel 2021、Excel 2024、Microsoft 365 與 Excel 網頁版。在 Excel 2019 或更舊的版本中不存在,檔案會在那裡顯示 #NAME?。VLOOKUP 在所有版本中都能使用。
簡單原則
當所有開啟檔案的人都使用 Excel 2021 或更新版本時,在新的工作中使用 XLOOKUP。當檔案必須能在舊版本中開啟,或你要維護仍然可以正常運作的既有 VLOOKUP 公式時,則使用 VLOOKUP(或搭配 MATCH 的 INDEX)。
常見錯誤
在 VLOOKUP 中省略 FALSE。 查找會變成大約符合,可能在沒有任何錯誤提示的情況下傳回錯誤的列。
修正: 除非你要大約符合,否則一律在最後一個引數寫上 FALSE(或 0)。
硬式編碼的欄號。 在表格內插入或刪除欄,會改變欄號所指向的欄位。
修正: 改用 XLOOKUP,或用 MATCH 取得欄號,讓公式跟著標題走。
常見問題
VLOOKUP 會被淘汰嗎?
不會。Microsoft 為了相容性保留它,而且它仍是既有活頁簿中最常見的查閱函數。在測試和共用檔案中,它仍然值得認識。
XLOOKUP 比 VLOOKUP 快嗎?
在日常的試算表中,差異不明顯。請依彈性,以及檔案需要在哪些 Excel 版本中開啟來選擇。
相關函數
- VLOOKUPVLOOKUP 會在表格的第一欄中找到一個值,並從同一列的另一欄傳回值。包含語法、範例、常見錯誤與練習。.
- XLOOKUPXLOOKUP 會在一欄或一列中搜尋,並從另一欄傳回對應的值,可以朝任何方向查詢,並內建「找不到」的訊息。包含語法、範例與練習。.
- INDEXINDEX 會傳回範圍中指定列與欄的值。搭配 MATCH 就能變成彈性的查詢。包含語法、範例,以及 INDEX/MATCH 的說明。.
- MATCHMATCH 會傳回值在清單中的位置,例如「蓮娜」是第 3 個名字。包含語法、比對類型、範例,以及搭配 INDEX 的雙向查詢。.
- IFERRORIFERROR 會在公式產生錯誤(例如 #N/A 或 #DIV/0!)時傳回你指定的值,否則傳回正常的結果。包含語法、範例,以及何時不該使用它。.
更多關於 VLOOKUP 與 XLOOKUP
本指南比較這兩個函數,並提供一個經驗法則。本篇則補充各自勝出的實際案例、如何安全地把舊的 VLOOKUP 遷移過去,以及兩者都會允許的無聲錯誤。
逐步轉換 VLOOKUP
假設一個 VLOOKUP 在表格 E2:G50 中查找 A2 的 ID,並傳回第 3 欄。要轉換它,請把表格替換為兩個獨立的範圍:要搜尋的欄,以及要傳回的欄。欄編號就不再需要了。
=VLOOKUP(A2,E2:G50,3,FALSE)
=XLOOKUP(A2,E2:E50,G2:G50,"not found")在把舊公式全面替換之前,請先用幾個 ID 測試新公式,包括一個不存在的 ID。
不會發出提示的錯誤
- 省略 VLOOKUP 的第四個引數,或寫入 TRUE,代表大約符合。若資料沒有排序,會傳回錯誤的值,而不是錯誤訊息。
- 在 VLOOKUP 表格中插入欄位,會無聲地改變數字 3 指向的欄位。
- 結尾的空格,或一邊是存成文字的 ID、另一邊是數字,會讓兩個函數都回報找不到。請檢查資料類型。
- 兩個函數都只傳回第一個符合的結果。如果鍵值重複,其他列就永遠不會被查到。
何時 VLOOKUP 仍然適用
XLOOKUP 需要 Excel 2021,Excel 2019 沒有它,更早的版本也沒有。如果檔案會由使用較舊版本的同事開啟,VLOOKUP,或 INDEX 與 MATCH 的組合,會比較安全。若需要大約比對和萬用字元比對,請參閱 [Excel 中的萬用字元](/guides/wildcards)。