Excel大量資料比對技巧：最強Excel函數XLOOKUP怎麼用？

按上述原理填入公式後，就會在F2欄位輸入=XLOOKUP(D2, $H$3:$H$7, $I$3:$I$7)，向下拖曳（複製公式）後，也可以得到答案。

在進行Excel大量資料比對時，XLOOKUP與VLOOKUP的差別？

Xlookup與Vlookup雖然都Excel函數，但功能上仍有些許差異。

差別1：Xlookup能解決所有查表問題

兩者的公式在第2個引數上擷取範圍不同，Vlookup輸入的是整個參考表範圍 (table_array)；Xlookup則是輸入尋找的列 (lookup_array)，這使Xlookup的公式有更大的彈性可以取代許多查詢函數。

要了解Xlookup有多強大，必須以Vlookup對比。

Vlookup公式有一項限制，被比對的欄位(table_array)一定要在參考表的第1欄。

下圖有3種售價參考表，若要符合Vlookup的限制，則售價參考表2或3的格式，都需要另外整理為參考表1才行，否則就會顯示錯誤訊息「#N/A」。

相較之下， Xlookup公式就沒有此限制，被比對的欄位可以抓取任何一欄，因此無論哪種格式皆可直接使用。

此外，若Vlookup遇到參考表3這種橫向表格時，就需改成另一個函數公式Hlookup才行；但Xlookup仍可用同一個既有公式來解決問題。

差別2：Xlookup可傳回多個專案陣列

Xlookup可以設定多個回傳資訊 (return_array)。如下圖，若想要以名字追蹤多個資料，可以設定回傳資訊範圍，只要輸入：=XLOOKUP(H3,B2:B11,B2:E11)，就可以一次回傳橫向資料「部門」、「銷售產品」和「銷售數量」等資訊。

差別3：Xlookup公式可以更精簡化

以前面舉例的查詢單價為例：

XLOOKUP()的公式如下：F2=XLOOKUP(D2, $H$3:$H$7, $I$3:$I$7)

VLOOKUP()的相等公式如下：F2=VLOOKUP(D2, $H$3:$I$7, 2, 0)

XLOOKUP因為已預設，所以不用再輸入「比對方式」 0 或 1，在公式輸入上，比VLOOKUP更精簡好懂。

你知道還有HLOOKUP嗎？

H 在 HLOOKUP 中表示「水平」，也就是在XLOOKUP函數問世之前，與VLOOKUP函數並稱職場最強函數。它與VLOOKUP的公式邏輯相同，只不過範圍的回寫方向不一樣而已，VLOOKUP函數以垂直搜尋，HLOOKUP是水平搜尋。

（延伸閱讀：Excel VLOOKUP教學：３大用法與常見錯誤）

除了XLOOKUP，還有哪些處理大量資料比對的函數？

1. INDEX + MATCH 函數：靈活又強大的經典搭配

不只上述函數，還有一組函數同樣能處理大量資料比對：INDEX 和 MATCH函數。

(責任編輯 / 杜韋樺、王穎皓、溫為翔)