VLOOKUP函數(shù)是所有使用Excel的朋友對(duì)非常熟悉的一個(gè)函數(shù)。盡管它有這樣那樣的缺陷，但是我們還是離不開(kāi)它。幾乎，在Excel的各種應(yīng)用場(chǎng)景中，我們都會(huì)發(fā)現(xiàn)它的身影。為了更好地使用這個(gè)函數(shù)解決各種實(shí)際問(wèn)題，我們還發(fā)明了很多方法，寫(xiě)出一個(gè)一個(gè)復(fù)雜又巧妙的公式。很多朋友對(duì)這些公式（也是對(duì)VLOOKUP）是愛(ài)恨交加，既認(rèn)為這個(gè)函數(shù)和這些公式可以解決自己那些難以處理的工作問(wèn)題，同時(shí)，又覺(jué)得這些復(fù)雜的公式難度有點(diǎn)高，很難理解，也很難記住，更不用說(shuō)舉一反三，靈活使用了。
現(xiàn)在，這一切已經(jīng)改變了。這些改變的發(fā)生都是源于一個(gè)新的函數(shù)：XLOOKUP。
2019年8月28日，微軟發(fā)布了一個(gè)新的Excel函數(shù)：XLOOKUP。作為L(zhǎng)OOKUP系列函數(shù)家族的新生力量，它可以比它的前輩更好的完成各種查找場(chǎng)景的工作。
與傳統(tǒng)的VLOOKUP函數(shù)相比，XLOOKUP函數(shù)至少具有下面的優(yōu)點(diǎn)：
XLOOKUP函數(shù)可以進(jìn)行“反向查找”。
VLOOKUP函數(shù)只能返回查找值右邊的列，如果要返回查找值左邊的列，要么結(jié)合其他的函數(shù)（或者使用數(shù)組），要么修改源數(shù)據(jù)。而XLOOKUP可以很靈活的返回查找區(qū)域的任意列。
XLOOKUP可以查找最后一個(gè)匹配值。
VLOOKUP函數(shù)只能返回第一個(gè)匹配值。在那些需要返回最后一個(gè)匹配值的場(chǎng)景中，需要大費(fèi)周章。
在查找區(qū)域中插入或者刪除列后，XLOOKUP公式可以自動(dòng)變化。
VLOOKUP函數(shù)的第三個(gè)參數(shù)是個(gè)數(shù)字，表示返回查找區(qū)域的相對(duì)列號(hào)。如果查找區(qū)域中插入和刪除列，這個(gè)數(shù)字不會(huì)自動(dòng)變化。
缺省情況下，XLOOKUP函數(shù)進(jìn)行精確匹配。
很多人使用VLOOKUP函數(shù)的錯(cuò)誤是由于省略最后一個(gè)參數(shù)造成的。因?yàn)?，最后一個(gè)參數(shù)表示匹配方式，如果省略這個(gè)參數(shù)，缺省值是近似匹配。這個(gè)“別扭”的設(shè)置導(dǎo)致我在每一個(gè)培訓(xùn)課上都會(huì)強(qiáng)調(diào)：不要省略最后一個(gè)參數(shù)。XLOOKUP函數(shù)沒(méi)有這個(gè)問(wèn)題了，因?yàn)槿笔∏闆r下，XLOOKUP函數(shù)進(jìn)行的是精確匹配。
XLOOKUP函數(shù)可以返回一個(gè)單元格區(qū)域。
XLOOKUP函數(shù)既可以像VLOOKUP函數(shù)一樣返回一個(gè)單元格，也可以像INDEX一樣返回一個(gè)區(qū)域。這是非常有用的特性。
XLOOKUP自帶錯(cuò)誤處理機(jī)制。
VLOOKUP找不到匹配結(jié)果時(shí)會(huì)返回#N/A錯(cuò)誤。需要使用IFERROR函數(shù)來(lái)處理。但是XLOOKUP不用這么麻煩！
說(shuō)了這么多，你是不是對(duì)XLOOKUP函數(shù)很有興趣了。下面我們一起看看這個(gè)函數(shù)是如何使用的。
01
XLOOKUP的語(yǔ)法
按照慣例，我們先來(lái)看看這個(gè)函數(shù)的語(yǔ)法：
這個(gè)函數(shù)有6個(gè)參數(shù)：
lookup_value
查找值，表示你希望用來(lái)匹配的條件?？梢允侵苯虞斎氲闹担部梢允菃卧褚?。
lookup_array
查找區(qū)域，是個(gè)數(shù)組或者單元格區(qū)域（只能一列或者一行），用來(lái)與查找值進(jìn)行比對(duì)。
return_array
返回區(qū)域。你希望返回的值所在的單元格區(qū)域或者數(shù)組（可以多列或多行）
if_not_found
如果沒(méi)有找到匹配值，XLOOKUP就返回這個(gè)參數(shù)。這個(gè)參數(shù)是可以省略的。如果省略，并且沒(méi)有找到匹配值，將返回#N/A
match_mode
匹配方式，有4個(gè)值可以選擇：0-精確匹配，-1-精確匹配或者比查找值小的值中最大的那個(gè)值，1-精確匹配或者比查找值大的值中最小的那個(gè)值，2-通配符匹配。缺省情況下，是精確匹配。
search_mode
搜索方式，有4個(gè)值可以選擇：1-從前往后搜索，-1-從后往前搜索，2-二分法搜索（升序），-2-二分法搜索（降序）。缺省情況下，是第一種搜索方式）—從前往后搜索。
單純看這些參數(shù)，可以有點(diǎn)隔靴搔癢。下面我們結(jié)合例子來(lái)看這個(gè)函數(shù)的使用和各參數(shù)的意義。
02
使用XLOOKUP的例子
例1 最簡(jiǎn)單的匹配查找
在這個(gè)例子中，我們使用XLOOKUP查找滿(mǎn)足B14中的值“芬達(dá)蘋(píng)果”的記錄，需要在C3:C10區(qū)域進(jìn)行匹配，返回E3:E10區(qū)域中的對(duì)應(yīng)值。這里可以看出，由于返回區(qū)域可以指定范圍而不是數(shù)字，從而可以進(jìn)行“反向查找”。由于后面的參數(shù)省略了，所有采用的是精確匹配。
這個(gè)公式的結(jié)果等價(jià)于公式：
=VLOOKUP(B14,C3:E10,3,0)例2 同時(shí)返回多項(xiàng)
乍看上去，這個(gè)例子跟例1一樣。但是仔細(xì)看，在這個(gè)例子中，我們使用一個(gè)公式返回了多列的結(jié)果（數(shù)量和金額），要點(diǎn)在與返回區(qū)域的參數(shù)從一列（E3:E10)變成了兩列(D3:E10)。由于返回了兩列的對(duì)應(yīng)值，因此，結(jié)果“溢出”了。
例3 匹配不成功的處理
前兩個(gè)例子中，沒(méi)有指定匹配不成功的處理方式，因此，如果找不到，就會(huì)返回#N/A
找不到，所有返回#N/A。由于匹配不成功，所以，盡管指定返回兩列，結(jié)果也沒(méi)有“溢出”。
如果不希望返回錯(cuò)誤值，可以使用第4個(gè)參數(shù)：
我們將第4個(gè)參數(shù)輸入文本“找不到”，這樣當(dāng)匹配不成功時(shí)，就會(huì)返回這個(gè)值。
例4 近似匹配
使用VLOOKUP函數(shù)進(jìn)行近似匹配最典型的例子就是個(gè)人所得稅的計(jì)算。XLOOKUP做起來(lái)也很簡(jiǎn)單：
這個(gè)公式將XLOOKUP函數(shù)的所有參數(shù)都寫(xiě)全了。如果匹配不成功，就返回0，第5個(gè)參數(shù)是-1，表示精確匹配或者比查找值小的最大值。最后一個(gè)參數(shù)表示從前往后查找。
這個(gè)公式本身的結(jié)果跟VLOOKUP的使用是一樣的。但是需要指出的是：最后一個(gè)參數(shù)的使用不影響查找的結(jié)果。在VLOOKUP函數(shù)中，如果要用近似匹配，查找區(qū)域必須按照第一列查找值進(jìn)行升序排序。而在XLOOKUP中，不再有這樣的要求了。
例5 返回動(dòng)態(tài)區(qū)域
在前面的例子中，返回區(qū)域都是寫(xiě)死的。實(shí)際上，我們經(jīng)常需要根據(jù)某個(gè)參數(shù)確定要返回的列。這是可以使用兩個(gè)XLOOKUP結(jié)合：
這里，我們使用了XLOOKUP公式來(lái)確定需要返回的結(jié)果，如果單步執(zhí)行，可以看到這個(gè)內(nèi)層的XLOOKUP公式返回的是D3:D10區(qū)域。
這個(gè)例子實(shí)際上提示我們，XLOOKUP函數(shù)可以返回一個(gè)區(qū)域。在下面的例子中，更好的說(shuō)明了這一點(diǎn)。
例6 返回一個(gè)區(qū)域
在這個(gè)例子中，第一個(gè)XLOOKUP返回的是D5單元格，而第二個(gè)XLOOKUP返回的是D11，因此可以使用SUM函數(shù)進(jìn)行D5:D11的求和。
例7 返回最后一個(gè)值：
在這個(gè)例子中，左邊一個(gè)XLOOKUP公式返回的是第一個(gè)匹配成功的結(jié)果。而右邊一個(gè)XLOOKUP返回的是最后一個(gè)匹配成功的結(jié)果。
03
總結(jié)和其他
XLOOKUP函數(shù)中還有一些其他要注意的地方，比如要進(jìn)行通配符的匹配時(shí)，必須將第5個(gè)參數(shù)指定為2。另外，這個(gè)函數(shù)返回多個(gè)不連續(xù)的列時(shí)需要結(jié)合CHOOSE等函數(shù)使用等。這里就沒(méi)有過(guò)多涉及。這些技巧有待大家熟悉后，逐漸挖掘設(shè)計(jì)。
值得一提的是，在官方的說(shuō)法中，XLOOKUP的精確匹配算法重新進(jìn)行了設(shè)計(jì)，所以速度非常快。有人說(shuō)精確匹配和近似匹配沒(méi)有明顯的差距了（大家知道，VLOOKUP的精確匹配和近似匹配的計(jì)算速度相差數(shù)百倍）。這個(gè)我沒(méi)有驗(yàn)證，根據(jù)我的理解，精確匹配的改進(jìn)主要是返回多列時(shí)的速度更快了。具體這方面的內(nèi)容等我有時(shí)間了進(jìn)行一下測(cè)試，再跟大家交流。如果大家有這方面的經(jīng)驗(yàn)，也可以留言告訴我。
Excel變簡(jiǎn)單，從此不加班！加入E學(xué)會(huì)，學(xué)習(xí)更多Excel函數(shù)和數(shù)據(jù)處理技巧。一次加入，永久有效。
聯(lián)系客服
