Excel 的 XLOOKUP 函數
XLOOKUP 會在一個範圍中尋找一個值,並從另一個範圍中傳回相同位置的值。與 VLOOKUP 不同,這兩個範圍可以位於任何位置:結果可以來自左側的欄,而且你不必計算欄號。
它可用於 Excel 2021、Excel 2024、Microsoft 365 與網頁版 Excel。Excel 2019 或更舊版本無法使用,因此與那些版本共用的檔案,仍然需要使用 VLOOKUP 或 INDEX/MATCH。
語法
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| 引數 | 代表什麼意思 |
|---|---|
| lookup_value | 要查找的內容。 |
| lookup_array | 要搜尋的欄(或列)。 |
| return_array | 要從中傳回值的欄(或列)。大小須與 lookup_array 相同。 |
| if_not_found選填 | 沒有符合時要顯示的內容,用來取代 #N/A。 |
| match_mode選填 | 0 = 完全符合(預設),-1 = 完全符合或下一個較小的值,1 = 完全符合或下一個較大的值,2 = 萬用字元符合。 |
| search_mode選填 | 1 = 從第一項開始搜尋(預設),-1 = 從最後一項開始搜尋,2 = 二分搜尋(清單由小到大排序),-2 = 二分搜尋(清單由大到小排序)。 |
範例
1. 依產品編號查價格
| 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:A5 中搜尋,並從價格欄 C2:C5 傳回。完全符合是預設值,因此不需要 FALSE。
2. 向左查詢
| 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 |
搜尋欄(B)位於傳回欄(A)的右側。VLOOKUP 無法做到這一點;XLOOKUP 可以。
3. 找不到時顯示你自己的訊息
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 產品編號 | 產品 | 價格 | 庫存 | 產品 P-999 | |
| 2 | P-101 | 桌燈 | 24.5 | 40 | 查無此產品 | |
| 3 | P-102 | 辦公椅 | 149 | 12 | ||
| 4 | P-103 | 螢幕架 | 39.9 | 25 | ||
| 5 | P-104 | 鍵盤 | 59 | 0 |
第 4 個引數取代了 #N/A,因此不需要在公式外層包上 IFERROR。
4. 大約符合:價格級距
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 起始重量(公斤) | 運費 | 包裹重量 | 6.3 | |
| 2 | 0 | 4.99 | 運費 | 12 | |
| 3 | 2 | 7.5 | |||
| 4 | 5 | 12 | |||
| 5 | 10 | 20 |
match_mode -1 代表「完全符合,否則取下一個較小的值」。6.3 不在清單中,因此使用 5 → 12。與 VLOOKUP 不同,即使清單沒有排序,這也能運作。
常見錯誤
#NAME? 錯誤。 該 Excel 版本沒有 XLOOKUP(Excel 2019 或更舊版本)。
修正: 需要在舊版本中開啟的檔案,請使用 VLOOKUP 或 INDEX/MATCH。
#VALUE! 錯誤。 查找範圍與傳回範圍的大小不同,例如 A2:A5 與 C2:C6。
修正: 讓兩個範圍從相同的列開始,並在相同的列結束。
複製公式時範圍位移。
修正: 用 $ 符號鎖定:
=XLOOKUP(G1,$A$2:$A$5,$C$2:$C$5)。
常見問題
XLOOKUP 與 VLOOKUP 有什麼不同?
XLOOKUP 使用各自獨立的搜尋範圍與傳回範圍(因此可以向左查詢),預設為完全符合,並內建「找不到」的值。VLOOKUP 則可在每個 Excel 版本中使用。
XLOOKUP 能一次傳回多欄嗎?
可以。如果 return_array 寬度為多欄,例如 B2:D5,在 Excel 2021 及更新版本中,結果會溢出到相鄰的儲存格。
如何找出最後一個符合的值,而不是第一個?
把 search_mode 設為 -1:=XLOOKUP(G1,A2:A5,C2:C5,"",0,-1)。
相關函數
- VLOOKUPVLOOKUP 會在表格的第一欄中找到一個值,並從同一列的另一欄傳回值。包含語法、範例、常見錯誤與練習。.
- INDEXINDEX 會傳回範圍中指定列與欄的值。搭配 MATCH 就能變成彈性的查詢。包含語法、範例,以及 INDEX/MATCH 的說明。.
- MATCHMATCH 會傳回值在清單中的位置,例如「蓮娜」是第 3 個名字。包含語法、比對類型、範例,以及搭配 INDEX 的雙向查詢。.
- IFERRORIFERROR 會在公式產生錯誤(例如 #N/A 或 #DIV/0!)時傳回你指定的值,否則傳回正常的結果。包含語法、範例,以及何時不該使用它。.
指南
- Excel 的萬用字元:*、? 與 ~ 用於部分比對Excel 萬用字元 * 與 ? 如何在 COUNTIF、SUMIF、MATCH 和 XLOOKUP 中比對部分文字,以及如何用 ~ 搜尋真正的星號或問號。
- 如何在 Excel 中比較兩個欄使用 =A2=B2 逐列比較 Excel 中的兩個欄,或使用 COUNTIF 與 XLOOKUP 找出同時出現在兩份清單中的值,並醒目提示差異。免費開始使用。
更多關於 XLOOKUP 深入解析
XLOOKUP 會在一個範圍中尋找某個值,並從另一個範圍傳回相符的值。它修正了 VLOOKUP 的弱點:預設為完全符合、可以向任何方向查找,並內建處理找不到值的機制。
引數
你要提供查閱值、要搜尋的範圍,以及要傳回的範圍。另外還有三個選用項目:找不到時要顯示什麼、比對模式,以及搜尋方向。
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100)
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,0)由於搜尋範圍和傳回範圍是分開的,插入欄位時不會出錯,而且答案可以位於查閱鍵的左邊。
比對與搜尋模式
match_mode 中 0 代表完全符合(預設),-1 代表完全符合或下一個較小值,1 代表完全符合或下一個較大值,2 代表萬用字元比對。search_mode 中 1 代表從第一個搜尋到最後一個(預設),-1 代表從底部開始搜尋,當值重複出現時會傳回最後一個符合的結果。
=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,0,-1)
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,0,0,-1)傳回多個欄位
把傳回範圍指向多個欄,結果就會橫向溢出:
=XLOOKUP(A2,$E$2:$E$100,$F$2:$H$100)這需要右側有足夠的空間。如果那裡的儲存格已被占用,就會出現 #SPILL!。
注意事項
- 它需要 Excel 2021 或 Microsoft 365。在較舊版本中開啟的檔案會顯示 #NAME?。如果你要與使用較舊版 Excel 的人共用,INDEX 搭配 MATCH 才是安全的選擇。
- 搜尋範圍和傳回範圍的長度必須相同,否則會產生 #VALUE!。
- 萬用字元模式會特別處理 ?、* 和 ~。請參閱 [萬用字元指南](/guides/wildcards)。
- 並排比較請參閱 [VLOOKUP 與 XLOOKUP](/guides/vlookup-vs-xlookup),而 [XLOOKUP 課程](/learn/xlookup) 有練習題。