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