【原】青出于藍的Excel函數：XLOOKUP

ExcelEasy 2021-02-03

展開全文

VLOOKUP函數是所有使用Excel的朋友對非常熟悉的一個函數。盡管它有這樣那樣的缺陷，但是我們還是離不開它。幾乎，在Excel的各種應用場景中，我們都會發現它的身影。為了更好地使用這個函數解決各種實際問題，我們還發明了很多方法，寫出一個一個復雜又巧妙的公式。很多朋友對這些公式（也是對VLOOKUP）是愛恨交加，既認為這個函數和這些公式可以解決自己那些難以處理的工作問題，同時，又覺得這些復雜的公式難度有點高，很難理解，也很難記住，更不用說舉一反三，靈活使用了。

現在，這一切已經改變了。這些改變的發生都是源于一個新的函數：XLOOKUP。

2019年8月28日，微軟發布了一個新的Excel函數：XLOOKUP。作為LOOKUP系列函數家族的新生力量，它可以比它的前輩更好的完成各種查找場景的工作。

與傳統的VLOOKUP函數相比，XLOOKUP函數至少具有下面的優點：

XLOOKUP函數可以進行“反向查找”。
VLOOKUP函數只能返回查找值右邊的列，如果要返回查找值左邊的列，要么結合其他的函數（或者使用數組），要么修改源數據。而XLOOKUP可以很靈活的返回查找區域的任意列。
XLOOKUP可以查找最后一個匹配值。
VLOOKUP函數只能返回第一個匹配值。在那些需要返回最后一個匹配值的場景中，需要大費周章。
在查找區域中插入或者刪除列后，XLOOKUP公式可以自動變化。
VLOOKUP函數的第三個參數是個數字，表示返回查找區域的相對列號。如果查找區域中插入和刪除列，這個數字不會自動變化。
缺省情況下，XLOOKUP函數進行精確匹配。
很多人使用VLOOKUP函數的錯誤是由于省略最后一個參數造成的。因為，最后一個參數表示匹配方式，如果省略這個參數，缺省值是近似匹配。這個“別扭”的設置導致我在每一個培訓課上都會強調：不要省略最后一個參數。XLOOKUP函數沒有這個問題了，因為缺省情況下，XLOOKUP函數進行的是精確匹配。
XLOOKUP函數可以返回一個單元格區域。
XLOOKUP函數既可以像VLOOKUP函數一樣返回一個單元格，也可以像INDEX一樣返回一個區域。這是非常有用的特性。
XLOOKUP自帶錯誤處理機制。
VLOOKUP找不到匹配結果時會返回#N/A錯誤。需要使用IFERROR函數來處理。但是XLOOKUP不用這么麻煩！

說了這么多，你是不是對XLOOKUP函數很有興趣了。下面我們一起看看這個函數是如何使用的。

XLOOKUP的語法

按照慣例，我們先來看看這個函數的語法：

這個函數有6個參數：

lookup_value
查找值，表示你希望用來匹配的條件。可以是直接輸入的值，也可以是單元格引用。
lookup_array
查找區域，是個數組或者單元格區域（只能一列或者一行），用來與查找值進行比對。
return_array
返回區域。你希望返回的值所在的單元格區域或者數組（可以多列或多行）
if_not_found
如果沒有找到匹配值，XLOOKUP就返回這個參數。這個參數是可以省略的。如果省略，并且沒有找到匹配值，將返回#N/A
match_mode
匹配方式，有4個值可以選擇：0-精確匹配，-1-精確匹配或者比查找值小的值中最大的那個值，1-精確匹配或者比查找值大的值中最小的那個值，2-通配符匹配。缺省情況下，是精確匹配。
search_mode
搜索方式，有4個值可以選擇：1-從前往后搜索，-1-從后往前搜索，2-二分法搜索（升序），-2-二分法搜索（降序）。缺省情況下，是第一種搜索方式）—從前往后搜索。

單純看這些參數，可以有點隔靴搔癢。下面我們結合例子來看這個函數的使用和各參數的意義。

使用XLOOKUP的例子

例1 最簡單的匹配查找

在這個例子中，我們使用XLOOKUP查找滿足B14中的值“芬達蘋果”的記錄，需要在C3:C10區域進行匹配，返回E3:E10區域中的對應值。這里可以看出，由于返回區域可以指定范圍而不是數字，從而可以進行“反向查找”。由于后面的參數省略了，所有采用的是精確匹配。

這個公式的結果等價于公式：

=VLOOKUP(B14,C3:E10,3,0)

例2 同時返回多項

乍看上去，這個例子跟例1一樣。但是仔細看，在這個例子中，我們使用一個公式返回了多列的結果（數量和金額），要點在與返回區域的參數從一列（E3:E10)變成了兩列(D3:E10)。由于返回了兩列的對應值，因此，結果“溢出”了。

例3 匹配不成功的處理

前兩個例子中，沒有指定匹配不成功的處理方式，因此，如果找不到，就會返回#N/A

找不到，所有返回#N/A。由于匹配不成功，所以，盡管指定返回兩列，結果也沒有“溢出”。

如果不希望返回錯誤值，可以使用第4個參數：

我們將第4個參數輸入文本“找不到”，這樣當匹配不成功時，就會返回這個值。

例4 近似匹配

使用VLOOKUP函數進行近似匹配最典型的例子就是個人所得稅的計算。XLOOKUP做起來也很簡單：

這個公式將XLOOKUP函數的所有參數都寫全了。如果匹配不成功，就返回0，第5個參數是-1，表示精確匹配或者比查找值小的最大值。最后一個參數表示從前往后查找。

這個公式本身的結果跟VLOOKUP的使用是一樣的。但是需要指出的是：最后一個參數的使用不影響查找的結果。在VLOOKUP函數中，如果要用近似匹配，查找區域必須按照第一列查找值進行升序排序。而在XLOOKUP中，不再有這樣的要求了。

例5 返回動態區域

在前面的例子中，返回區域都是寫死的。實際上，我們經常需要根據某個參數確定要返回的列。這是可以使用兩個XLOOKUP結合：

這里，我們使用了XLOOKUP公式來確定需要返回的結果，如果單步執行，可以看到這個內層的XLOOKUP公式返回的是D3:D10區域。

這個例子實際上提示我們，XLOOKUP函數可以返回一個區域。在下面的例子中，更好的說明了這一點。

例6 返回一個區域

在這個例子中，第一個XLOOKUP返回的是D5單元格，而第二個XLOOKUP返回的是D11，因此可以使用SUM函數進行D5:D11的求和。

例7 返回最后一個值：

在這個例子中，左邊一個XLOOKUP公式返回的是第一個匹配成功的結果。而右邊一個XLOOKUP返回的是最后一個匹配成功的結果。

總結和其他

XLOOKUP函數中還有一些其他要注意的地方，比如要進行通配符的匹配時，必須將第5個參數指定為2。另外，這個函數返回多個不連續的列時需要結合CHOOSE等函數使用等。這里就沒有過多涉及。這些技巧有待大家熟悉后，逐漸挖掘設計。

值得一提的是，在官方的說法中，XLOOKUP的精確匹配算法重新進行了設計，所以速度非常快。有人說精確匹配和近似匹配沒有明顯的差距了（大家知道，VLOOKUP的精確匹配和近似匹配的計算速度相差數百倍）。這個我沒有驗證，根據我的理解，精確匹配的改進主要是返回多列時的速度更快了。具體這方面的內容等我有時間了進行一下測試，再跟大家交流。如果大家有這方面的經驗，也可以留言告訴我。

Excel變簡單，從此不加班！加入E學會，學習更多Excel函數和數據處理技巧。一次加入，永久有效。