XLOOKUP函數與VLOOKUP函數用法比較

本文從9個方面解讀XLOOKUP函數與VLOOKUP/LOOKUP函數之間的用法差異，包括：基礎語法、橫向查詢、縱向查詢、一對多、返回多值、反向查詢、多條件查詢、模糊查詢、近似查詢、匹配最後一個等等。

1、XLOOKUP基礎語法

在學習任何一個函數之前，需要了解這個函數的基礎語法，從微軟官方的幫助文件裡找到了這個函數的引數語法，共計有5個引數，跟LOOKUP的引數非常接近，但是使用起來會更加簡單了一些。

其中第1~3個引數跟LOOKUP的引數非常接近，都是將「查詢區域」和「結果區域」全部獨立出來了，跟VLOOKUP的「選擇區域」就有所差異，拆分出來會讓函數更加靈活。

第4個引數match_mode表示匹配型別，可以使用「精確匹配」「萬用字元匹配」「2種近似匹配」，預設為0表示精確匹配。

第5個引數search_mode是新增的一個引數，表示搜尋模式，在傳統的VLOOKUP/LOOKUP系列函數中，搜尋只能從上往下，並且尋找第2個、最後1個數值的時候會非常麻煩。而XLOOKUP就直接引進了這個引數，預設為1表示從上往下開始搜尋，-1表示從下往上搜尋，這2個會用的比較多一些。

至此，XLOOKUP的語法就初步了解了，接下來來實操下這個XLOOKUP函數有多強大!

2、基礎縱向查詢

例如下圖查詢“工號的電腦銷售額”，在基礎操作上VLOOKUP和XLOOKUP沒有什麼太大的差異，寫法都非常簡單，只是XLOOKUP將選擇區域和返回區域拆分出來單獨寫了而已。

來總結下XLOOKUP基礎縱向查詢的套路：

結論：雙方平均(VLOOKUP公式會簡潔一點點，不過差異不大)

3、基礎橫向查詢

既然能縱向查詢，那麼橫向查詢其實也是可以的。然而在VLOOKUP中，如果要實現橫向查詢會非常麻煩，又是需要構建虛擬陣列。在這裡我們使用INDEX+MATCH來代替VLOOKUP實現「基礎橫向查詢」。

而使用XLOOKUP就非常簡單了，所有操作都跟縱向查詢沒有任何差異，XLOOKUP會自動識別是什麼方向，例如將上面的資料橫放了，變成這個樣子：

在橫向查詢中XLOOKUP完爆VLOOKUP，XLOOKUP的兩個方向查詢用法一模一樣，沒有任何差異，太智慧了。

來總結下XLOOKUP橫向查詢的公式套路：

結論：XLOOKUP完爆VLOOKUP，因為VLOOKUP實現非常麻煩。

4、反向匹配查詢

在前面的語法中，我們說到XLOOKUP將「查詢區域」和「結果區域」全部獨立出來了，所以在這裡的話，反向匹配對於XLOOKUP來說沒有什麼障礙，相反VLOOKUP就需要去構建一個IF虛擬陣列來實現了。

來感受下2個函數的用法：

VLOOKUP是通過IF({1,0},XXX,XXX)的方式構建一個虛擬陣列來實現這個功能的，因為檢索關鍵字必須在選擇區域的第一列，對於新手來說理解非常不友好，而且複雜的陣列公式還會消耗大量的計算機資源，XLOOKUP依然是這麼牛逼完爆，格式沒有什麼變化，輕鬆完成反向查詢：

結論：XLOOKUP完爆VLOOKUP，基礎語法即可實現。

5、多條件查詢

多條件查詢是很多VLOOKUP的初學者的噩夢，需要寫非常複雜的IF陣列公式，而且又要注意定位參照的方式，而XLOOKUP使用起來就非常舒服了，只需要將多個條件利用&符號拼接起來就ok。

徹底告別複雜公式：

相信很多同學看到了VLOOKUP多條件查詢都是一臉懵逼，IF到底是什麼鬼，怎麼老是出現?其實這裡還是構建了一個虛擬陣列，陣列公式對於新手來說理解起來的確太困難。

將絕對定位去掉，我們來看下XLOOKUP公式究竟有多簡潔，絲毫不拖泥帶水：

=XLOOKUP(F4&G4, B4:B17&C4:C17, D4:D17, 0, 1)

總結XLOOKUP多條件查詢公式套路：

結論：XLOOKUP再次完爆VLOOKUP，簡潔速度快!

6、模糊查詢匹配

VLOOKUP和XLOOKUP均支援模糊匹配，在Excel中使用模糊匹配需要用到萬用字元(*、?、~)，這次兩個函數不相上下，XLOOKUP只需要將第4個引數修改成2表示萬用字元匹配即可。

兩個函數不相上下，因為這個功能比較簡單，總結下XLOOKUP的模糊查詢公式套路：

結論：XLOOKUP和VLOOKUP不相上下，因為都比較基礎

7、匹配最後一個值

在某些情況下，我們需要找到記錄裡的最後一條資料，而恰好XLOOKUP的最後一個引數是搜尋模式，只要我們將第5個引數search_mode修改成-1，就會倒序查詢，這樣就能找到最後一個數值了，非常簡單。

而VLOOKUP本身實現匹配最後一個值非常麻煩，這裡我們使用LOOKUP來代替：

LOOKUP的寫法就非常難理解了，又是用0除，又是做邏輯符號判斷等於的。對於新手實在太不友好，相比之下XLOOKUP的寫法就非常簡單了。直接將搜尋模式一改就ok了，這個功能在人事應用中非常廣，找到最後一次打卡時間和第一次打卡時間，非常簡單。

套路總結：

結論：XLOOKUP完爆VLOOKUP/LOOKUP，寫法簡潔，計算快

8、查詢多個值

查詢多個值無論是利用VLOOKUP還是LOOKUP實現起來都非常麻煩，因為需要構建一個IF虛擬陣列，而XLOOKUP就很便捷了。還記得我們的第一個引數叫“檢索關鍵字”麼?我們只需要將這個引數選中想要查詢的多個值就ok了。

當然的話，一般查詢多個值會對這些值做一個聚合運算，例如找到最大值、平均值、最小值等等。例如下方找到3個員工的銷售額最大值，就非常簡單：

而VLOOKUP又是要構建一個非常複雜的陣列公式，甚至還用到了T函數，理解起來真是太吃力了。總結套路：

結論：XLOOKUP再次完爆VLOOKUP，寫法非常簡單。

9、查詢返回多列

上面一個是查詢多個值，對這個命題擴充套件下，就可以得到返回多列。在XLOOKUP函數中返回多列套路也非常簡單，只需要將「結果區域」選擇多列就ok。而VLOOKUP需要使用ROW或者COLUMN函數才能實現。

例如想找到某個工號的平均銷售額，這裡需要同時返回電腦和手機的銷售額：

整體來說XLOOKUP函數比VLOOKUP函數理解起來更加簡潔，因為VLOOKUP函數使用了ROW作為輔助函數，對於新手來說，理解起來需要一定的門檻，總結：

當然的 XLOOKUP函數的用法還有非常多種，例如還可以通過修改第4個引數實現「近似匹配-包含/不包含最小值」，還可以利用XLOOKUP函數代替MATCH+INDEX實現篩選功能，甚至連Offset這個動態構數函數都可以代替。