【Excel教學】xlookup 用法與範例：比vlookup更簡單好用的比對函數

一、xlooup函數和vlookup函數的比較

xlookup函數相對vlookup有了更多人性化的設計，所以使用上也多了很多彈性，以下簡單列幾項主要差別:

1. 回傳的項目可以是一筆資料(某個儲存格)，也可以是回傳好幾筆資料(好幾個儲存格)。
2. 鍵值(被比對的欄位)不一定要在對照表格範圍的第1欄。
3. 不用指定TRUE(大約符合)或FALSE(完全符合)，預設就是FALSE。

關於vlookup函數的使用和錯誤處理可以先參考:

【Excel】vlookup函數#N/A的常見原因和解決方法

【Excel】vlookup函數用法和範例：比對重複資料並聯結 

二、xlookup函數語法

xlookup會在lookup_array範圍中，依序比對是否有跟lookup_value一樣的資料值，有的話會回傳return_array裡面的資料。

XLOOKUP( lookup_value , lookup_array , return_array , [if_not_found] , [match_mode] , [search_mode])

lookup_value : 要被比對的欄位，也就是要拿來當關聯的資料欄位
lookup_array : 要跟lookup_value做比對的欄位範圍
return_array: 要回傳的資料(可以是很多欄位)
[if_not_found]: 選填項。如果比對不到符合資料時，會回傳的值
[match_mode]: 選填項。判斷相符的模式，預設為完全相符。
[search_mode] : 選填項。指定要使用的搜尋模式。

三、xlookup函數範例

假設有兩份資料需要做對照，一份是會員購買紀錄，只儲存最近購買項目和會員編號；另一份會員資料儲存詳細會員資料:

(1) 會員購買紀錄 : 包括消費項目(A欄)、會員編號(B欄)。

(2) 會員資料 : 居住地名稱(H欄)、顧客編號(I欄)、平均每月消費(J欄)、性別(K欄)、年齡(L欄)

(一) 範例一：只回傳一筆資料 (居住地)

1. 會員編號在B欄，這欄是要被比對的資料欄位，所以lookup_value參數輸入B2。
2. 第2份資料表的會員編號在I欄，lookup_array參數輸入$I$2:$I$23，指定確切範圍的話要記得用「$」符號指定固定範圍，不然在使用自動填入的時候會出錯。
3. 第2份資料表的居住地在H欄，要回傳的資料return_array 參數輸入$H$2:$H$23。
4. 比對不到資料的時候希望可以直接顯示無法比對，所以if_not_found輸入”無法比對”。

=XLOOKUP( B2 , $I$2:$I$23 , $H$2:$H$23 , "無法比對")

沒有比對到符合資料，欄位會被填入無法比對。由於KJ19不在I 欄(顧客編號)裡面任何一筆，所以會顯示無法比對。

(二) 範例二：回傳多筆資料

基本上，跟前面回傳單筆的參數一樣，只是原本是只回傳H欄，現在是回傳H欄到L欄，所以要回傳的資料return_array 參數改為輸入$H$2:$L$23。

=XLOOKUP( B2 , $I$2:$I$23 , $H$2:$L$23 , "無法比對" )

下圖可以看到比對到的資料不只會回傳居住地，還會一併填入平均每月消費、性別、年齡等。不過顧客編號又被再次填入，因為它也在回傳範圍內，所以雖然顧客編號不用一定要在第1欄，但還是把它放在第1欄或最後1欄(最左邊或最右邊)比較好。

(三) 範例二：本文所用的範例檔案下載處

【Excel教學】範例檔案：xlookup

更多的Excel應用，歡迎參考我的Excel教學系列。