Excel XLOOKUP 函數用法與範例：取代 VLOOKUP

XLOOKUP 函數用於在資料表或範圍中尋找特定項目，並傳回同一列中另一個欄位的值。與舊版函數不同，它不需要指定欄數，且預設為完全比對，讓跨欄查詢變得更直覺且不容易出錯。

Excel XLOOKUP 函數語法

=XLOOKUP(查閱值, 查閱陣列, 傳回陣列, [找不到時傳回], [比對模式], [搜尋模式])

參數	必填	說明
查閱值	是	要搜尋的目標值。
查閱陣列	是	要在其中搜尋的範圍或陣列。
傳回陣列	是	包含要傳回結果的範圍或陣列。
找不到時傳回	選填	找不到相符項目時要顯示的文字或值。若省略則傳回 #N/A 錯誤。
比對模式	選填	指定比對方式。0 為完全比對（預設），-1 為完全比對或下一個較小項目，1 為完全比對或下一個較大項目，2 為萬用字元比對。
搜尋模式	選填	指定搜尋方向。1 為從第一筆開始（預設），-1 為從最後一筆開始，2 與 -2 為二元搜尋。

回傳：傳回與查閱值相符的對應項目。若傳回陣列包含多欄或多列，會以動態陣列溢出顯示。

適用版本：Microsoft 365、Excel 2021 以上版本支援。

XLOOKUP 函數範例

範例 1：基本向左查詢

根據員工編號查詢姓名。

姓名	部門	員工編號	查詢編號	查詢結果
王小明	業務部	E001	E003
李大華	行銷部	E002
張美麗	資訊部	E003

在 E2 輸入公式：

=XLOOKUP(D2, C2:C4, A2:A4)

結果：張美麗。XLOOKUP 可以輕鬆實現向左查詢。公式在 C2:C4 尋找 D2 的值（E003），並傳回 A2:A4 中對應的姓名「張美麗」。

範例 2：自訂找不到時的傳回值

查詢商品庫存，若找不到則顯示自訂提示。

商品名稱	庫存量	查詢商品	查詢結果
蘋果	50	橘子
香蕉	30
芭樂	20

在 D2 輸入公式：

=XLOOKUP(C2, A2:A4, B2:B4, "無此商品")

結果：無此商品。橘子不在 A2:A4 的清單中。透過設定第四個參數為「無此商品」，當找不到相符資料時，會直接顯示該文字取代 #N/A 錯誤。

範例 3：近似比對查詢

根據業績金額查詢對應的獎金比例（找下一個較小項目）。

業績門檻	獎金比例	實際業績	對應比例
0	0	45000
10000	0.05
50000	0.1
100000	0.15

在 D2 輸入公式：

=XLOOKUP(C2, A2:A5, B2:B5, , -1)

結果：0.05。業績 45000 介於 10000 與 50000 之間。設定比對模式為 -1，會尋找完全比對或下一個較小的值（10000），傳回對應的獎金比例 0.05。

使用 XLOOKUP 的技巧與注意事項

• XLOOKUP 預設為完全比對，不需要像 VLOOKUP 一樣在最後加上 0 或 FALSE。
• 當傳回陣列包含多個欄位時，XLOOKUP 會一次傳回整列資料，這在需要同時查詢多項資訊時非常方便。
• 利用搜尋模式參數設為 -1，可以從資料表底部往上搜尋，適合用來尋找同一項目的最新或最後一筆紀錄。

XLOOKUP 常見錯誤與解決方法

錯誤	原因	解決方法
#N/A	找不到相符的查閱值，且未設定「找不到時傳回」參數。	確認查閱值是否存在於查閱陣列中，或在第四個參數設定自訂的錯誤提示文字。
#VALUE!	查閱陣列與傳回陣列的維度（列數或欄數）不一致。	檢查並修改公式，確保查閱陣列與傳回陣列的範圍大小完全相同。
#SPILL!	傳回結果包含多個儲存格（陣列），但旁邊的儲存格已有資料阻擋。	清除阻擋範圍內的資料，讓陣列結果有足夠的空白空間可以溢出顯示。

Google 試算表可以用 XLOOKUP 嗎？

Google 試算表已全面支援 XLOOKUP 函數，語法與參數設定與 Excel 完全相同，可無縫轉換使用。

下載 XLOOKUP 範例檔

上面的範例都整理在這個檔案裡，每個範例一個工作表，黃色格子是公式，可以直接改資料看結果變化：XLOOKUP 範例.xlsx（範例公式的結果經過兩個模型交叉核對）。

常見問題