★
編按
★
這個函數(shù)本身是有一點難度的，一共有六個參數(shù)，這固然使函數(shù)具備了強大功能，但也讓一些初學(xué)者望而卻步。今天就通過一些我們平時常見的案例，來和大家一起了解XLOOKUP的基本用法。
HLOOKUP函數(shù)的基本結(jié)構(gòu)是：
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
翻譯成大白話就是：
=XLOOKUP(查找值,查找范圍,結(jié)果范圍,[找不到時顯示的值],[匹配方式],[查詢模式])
在這六個參數(shù)中，前三個是必須的，后面三個根據(jù)自己的需要選擇使用。
示例1：常規(guī)匹配（對標LOOKUP）
按照姓名匹配入職日期，公式為=XLOOKUP(G2,B:B,C:C)。
這種用法和LOOKUP的使用結(jié)構(gòu)非常像，但是有本質(zhì)的區(qū)別。LOOKUP函數(shù)要求查找范圍必須升序排列，而XLOOKUP則無此限制。就這個問題使用LOOKUP的結(jié)果如圖所示：
關(guān)于LOOKUP的用法不在本文討論范圍，請參閱公眾號之前的相關(guān)教程。
同事打死不愿說，到底這Excel函數(shù)有什么玄機？我看完才懂！
再比如按姓名匹配員工ID，這在VLOOKUP的用法中叫反向查找，XLOOKUP還是一樣的用法，公式為=XLOOKUP(G2,B:B,A:A)。
這是給大家介紹的第一種用法，XLOOKUP（查找值，查找范圍，結(jié)果范圍），沒有順序要求，沒有方向要求，這體驗一下子就超過了VLOOKUP和LOOKUP。
示例2：一次查找多個值（數(shù)組用法）
如果XLOOKUP的第一參數(shù)選擇一個單元格區(qū)域，可以對應(yīng)得到多個結(jié)果。在Excel365中更容易看到這種數(shù)組自動擴展的效果。
在此提醒那些用VLOOKUP時，第一參數(shù)習(xí)慣選一列的朋友，如果你這樣用XLOOKUP的話，電腦能卡死！
XLOOKUP的這種特性非常重要，比如要統(tǒng)計某幾個人的崗位津貼總和，就可以直接用公式=SUM(XLOOKUP(F2:F5,A:A,D:D))得到結(jié)果，這個公式非365用戶需要按Ctrl+Shift+Enter三鍵。
示例3：第四參數(shù)的妙用
來看這個例子，按照姓名找對應(yīng)的成績，當(dāng)出現(xiàn)數(shù)據(jù)源中不存在的姓名是，結(jié)果為#N/A。
通常遇到這種情況我們的第一反應(yīng)是外面嵌套一個IFERROR函數(shù)，實際上XLOOKUP的第四參數(shù)就可以取代IFERROR函數(shù)了。
公式修改為=XLOOKUP(D2,A:A,B:B,"姓名有誤")。
對于這個參數(shù)，我想大家都很容易掌握，畢竟使用一個參數(shù)就能少嵌套一個函數(shù)，這是非常好的體驗。
示例4：多樣的匹配方式
XLOOKUP提供了四種匹配方式。
從函數(shù)自帶的提示不難看出四種匹配方式的意思。0或者省略是精確匹配，之前的例子都是這種方式。-1是精確匹配或下一個較小的項，例如按照成績匹配等級，可以使用公式=XLOOKUP(B2,F:F,G:G,"",-1)。
這個公式的意思是在F列中找52，找不到的時候就找小于52的一個值，也就是0，最后得到的結(jié)果就是0所對應(yīng)的等級。如果就這樣看的話，似乎用LOOKUP更簡單。
但是LOOKUP要求查找范圍升序，假如數(shù)據(jù)變成這樣，結(jié)果就全錯了。
可以看出XLOOKUP函數(shù)完全不受順序的影響，LOOKUP則多了一些限制。
如果匹配方式用1的話則正好相反，找不到要找的值時，則會找較大的一個值。例如公式=XLOOKUP(B2,F:F,G:G,"",1)就會得到這樣的結(jié)果。
從這個例子可以看出，在做區(qū)間匹配時，-1對應(yīng)下限值，1對應(yīng)上限值。
示例5：使用通配符
有時候在匹配數(shù)據(jù)時會用到通配符，例如根據(jù)單位檢查匹配對應(yīng)的業(yè)務(wù)人員，公式為=XLOOKUP("*"&D2&"*",A:A,B:B,"無對應(yīng)人員",2)。
XLOOKUP函數(shù)默認不支持通配符的，如果要用通配符，第五參數(shù)必須填2，這也是XLOOKUP函數(shù)的一個特殊之處。
示例6：多種查詢方式
查詢方式和匹配方式是不一樣的概念，XLOOKUP提供了四種查詢方式：
查詢方式1是從上向下，默認的也是這種方式。查詢方式-1是從下向上，如果要查找的值是唯一的，那么這兩種方式得到的結(jié)果是一樣的，但是當(dāng)要查找的值有多個的時，兩種方式的區(qū)別就出現(xiàn)了。
例如公式=XLOOKUP(E2,B:B,C:C,"",0,1)得到的就是每個人的首日銷量。
而公式=XLOOKUP(E2,B:B,C:C,"",0,-1)得到的則是每個人的末日銷量。
這兩種查詢方式都是遍歷法原理，只是查詢方向的區(qū)別，而查詢方式2和-2，則用的是二分法原理，區(qū)別就是二分法的時候默認升序還是降序。關(guān)于遍歷法和二分法原理，理論性較強，可以參考之前的這篇教程，本文就不贅述了。久等！VLOOKUP坐字法合并單元格查找的秘密終于破了！
示例7：橫向匹配和多列匹配
橫向查找之前多是用HLOOKUP函數(shù)來解決，現(xiàn)在也可以用XLOOKUP，只要查找范圍和結(jié)果范圍是橫向的就行。例如公式=XLOOKUP(B6,1:1,2:2)就是橫向查找的結(jié)果。
在沒有XLOOKUP的時候，我們使用VLOOKUP做多列匹配往往要用到COLUMN函數(shù)，現(xiàn)在就方便了，只要將結(jié)果區(qū)域選擇多列即可，注意這種用法只能對連續(xù)的多列匹配適用。
以上就是XLOOKUP函數(shù)的基本用法，功能確實很多也很強大，希望有條件的伙伴能夠趕緊練起來，也許在你使用的過程中有什么心得也歡迎留言和大家分享。
閱讀推薦
關(guān)注我們，發(fā)現(xiàn)更多Excel優(yōu)質(zhì)教程
比VBA好用100倍！拆分工作表，用數(shù)據(jù)透視表5秒就搞定！
靠一只“豬”一秒拆分上千個工作表？！同事的騷操作看呆我......
不懂這個“人類高質(zhì)量Excel技巧”，就不要輕易在簡歷上寫“精通Excel”
