編按：

XLOOKUP函數出現已經有近一年的時間了，在這個函數的資料剛被爆出來的時候，曾經引起了不小的轟動，很多人宣稱這家伙將徹底淘汰VLOOKUP，甚至有人說微軟即將把VLOOKUP拋棄了……然而實際情況卻并非如此，XLOOKUP函數的存在感非常非常低，最重要的原因是因為這個函數當時只存在于最新的Excel365版本，當時大家使用的其他各個版本的Excel都無法使用該函數。不過現在Excel2021版本也正式發(fā)布了，這就為XLOOKUP函數的普及帶來了機會。

這個函數本身是有一點難度的，一共有六個參數，這固然使函數具備了強大功能，但也讓一些初學者望而卻步。今天就通過一些我們平時常見的案例，來和大家一起了解XLOOKUP的基本用法。

HLOOKUP函數的基本結構是：

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

翻譯成大白話就是：

=XLOOKUP(查找值,查找范圍,結果范圍,[找不到時顯示的值],[匹配方式],[查詢模式])

在這六個參數中，前三個是必須的，后面三個根據自己的需要選擇使用。

示例1：常規(guī)匹配（對標LOOKUP）

按照姓名匹配入職日期，公式為=XLOOKUP(G2,B:B,C:C)。

這種用法和LOOKUP的使用結構非常像，但是有本質的區(qū)別。LOOKUP函數要求查找范圍必須升序排列，而XLOOKUP則無此限制。就這個問題使用LOOKUP的結果如圖所示：

再比如按姓名匹配員工ID，這在VLOOKUP的用法中叫反向查找，XLOOKUP還是一樣的用法，公式為=XLOOKUP(G2,B:B,A:A)。

這是給大家介紹的第一種用法，XLOOKUP（查找值，查找范圍，結果范圍），沒有順序要求，沒有方向要求，這體驗一下子就超過了VLOOKUP和LOOKUP。

示例2：一次查找多個值（數組用法）

如果XLOOKUP的第一參數選擇一個單元格區(qū)域的話，可以對應得到多個結果。在Excel365中更容易看到這種數組自動擴展的效果。

在此提醒那些用VLOOKUP時，第一參數習慣選一列的朋友，如果你這樣用XLOOKUP的話，電腦能卡死！XLOOKUP的這種特性非常重要，比如要統(tǒng)計某幾個人的崗位津貼總和，就可以直接用公式=SUM(XLOOKUP(F2:F5,A:A,D:D))得到結果，這個公式非365用戶需要按Ctrl+Shift+Enter三鍵。

示例3：第四參數的妙用

來看這個例子，按照姓名找對應的成績，當出現數據源中不存在的姓名是，結果為#N/A。

通常遇到這種情況我們的第一反應是外面嵌套一個IFERROR函數，實際上XLOOKUP的第四參數就可以取代IFERROR函數了。

公式修改為=XLOOKUP(D2,A:A,B:B,"姓名有誤")。

對于這個參數，我想大家都很容易掌握，畢竟使用一個參數就能少嵌套一個函數，這是非常好的體驗。

示例4：多樣的匹配方式

XLOOKUP提供了四種匹配方式。

從函數自帶的提示不難看出四種匹配方式的意思。0或者省略是精確匹配，之前的例子都是這種方式。-1是精確匹配或下一個較小的項，例如按照成績匹配等級，可以使用公式=XLOOKUP(B2,F:F,G:G,"",-1)。

這個公式的意思是在F列中找52，找不到的時候就找小于52的一個值，也就是0，最后得到的結果就是0所對應的等級。

如果就這樣看的話，似乎用LOOKUP更簡單。

但是LOOKUP要求查找范圍升序，假如數據變成這樣的話，結果就全錯了。

可以看出XLOOKUP函數完全不受順序的影響，LOOKUP則多了一些限制。

如果匹配方式用1的話則正好相反，找不到要找的值時，則會找較大的一個值。例如公式=XLOOKUP(B2,F:F,G:G,"",1)就會得到這樣的結果。

從這個例子可以看出，在做區(qū)間匹配時，-1對應下限值，1對應上限值。

示例5：使用通配符

有時候在匹配數據時會用到通配符，例如根據單位檢查匹配對應的業(yè)務人員，公式為=XLOOKUP("*"&D2&"*",A:A,B:B,"無對應人員",2)。

XLOOKUP函數默認不支持通配符的，如果要用通配符，第五參數必須填2，這也是XLOOKUP函數的一個特殊之處。

示例6：多種查詢方式

查詢方式和匹配方式是不一樣的概念，XLOOKUP提供了四種查詢方式：

查詢方式1是從上向下，默認的也是這種方式。查詢方式-1是從下向上，如果要查找的值是唯一的，那么這兩種方式得到的結果是一樣的，但是當要查找的值有多個的時候，兩種方式的區(qū)別就出現了。

例如公式=XLOOKUP(E2,B:B,C:C,"",0,1)得到的就是每個人的首日銷量。

而公式=XLOOKUP(E2,B:B,C:C,"",0,-1)得到的則是每個人的末日銷量。

這兩種查詢方式都是遍歷法原理，只是查詢方向的區(qū)別，而查詢方式2和-2，則用的是二分法原理，區(qū)別就是二分法的時候默認升序還是降序。關于遍歷法和二分法原理，理論性較強，可以參考之前的這篇教程，本文就不贅述了。

https://mp.weixin.qq.com/s/ODH8z5EhM5lnZ5J7PH-Twg

示例7：橫向匹配和多列匹配

橫向查找之前多是用HLOOKUP函數來解決，現在也可以用XLOOKUP，只要查找范圍和結果范圍是橫向的就行。例如公式=XLOOKUP(B6,1:1,2:2)就是橫向查找的結果。

在沒有XLOOKUP的時候，我們使用VLOOKUP做多列匹配往往要用到COLUMN函數，現在就方便了，只要將結果區(qū)域選擇多列即可，注意這種用法只能對連續(xù)的多列匹配適用。

以上就是XLOOKUP函數的基本用法，功能確實很多也很強大，希望有條件的伙伴能夠趕緊練起來。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

７個Excel小技巧，提高表格查看效率

Excel運用規(guī)范1：一個單元格只記錄一條信息

快速整理不規(guī)范的Excel表格的7個公式

9條最實用的計算excel中關于日期的公式?。ńㄗh收藏）

版權申明：

本文作者郅龍；同時部落窩教育享有專有使用權。若需轉載請聯系部落窩教育。