突發(fā)！34歲Vlookup函數(shù)退休！替代者好用10倍！會計必須重新學習了！

突發(fā)，34歲的VLOOKUP要退出EXCEL圈了！

2019年8月28日微軟發(fā)布了XLOOKUP，同時支持豎向和橫向查找、從左向右、從右向左查找。

這標志著34歲的VLOOKUP將正式退出歷史舞臺！

XLOOKUP的面市，將不僅代替VLOOKUP，LOOKUP、HLOOKUP函數(shù)可能也將退出Excel。

功能強大的VLOOKUP函數(shù)

做財務會計的，熟悉的Excel函數(shù)除了SUM和IF外，VLOOKUP是最常用的一個函數(shù)。

VLOOKUP被稱為Excel中的效率之王，但是95%的Excel使用者都不能很好使用VLOOKUP。但是，VLOOKUP函數(shù)又是Excel中的大眾情人。有平臺曾經(jīng)做過“如果只能選擇學習Excel中的一項功能，你會選擇哪個”的調(diào)查，VLOOKUP函數(shù)竟然高居第二位。

在我們的工作中，基本每天都會遇到這樣的場景。比如從總表中，根據(jù)姓名匹配身份證號信息，根據(jù)考核等級確定獎金比例。

這些工作本質(zhì)上都是匹配調(diào)用：匹配同樣的數(shù)據(jù)，調(diào)用出我們需要的數(shù)據(jù)。要解決這個問題，最常用到的就是VLOOKUP函數(shù)。

那么VLOOKUP函數(shù)究竟如何使用呢？

VLOOKUP函數(shù)語法結(jié)構(gòu)：VLOOKUP(Lookup_value,Table_array,Col_index_number,Range_lookup),即VLOOKUP（查找值，查找范圍，返回的數(shù)值所在的列數(shù)，精確匹配還是模糊匹配）。

一、VLOOKUP函數(shù)精確匹配，返回你需要的唯一數(shù)據(jù)

比如根據(jù)姓名匹配身份證號信息，對于這種匹配調(diào)用唯一的數(shù)據(jù)，就要用到VLOOKUP函數(shù)的精確匹配了。

操作步驟：查找范圍為絕對引用，可按快捷鍵F4，精確匹配下參數(shù)為0或FALSE。

注意事項：查找范圍和要返回的數(shù)值所在的列數(shù)都是要從查找值所在的列開始計算。

VLOOKUP查詢調(diào)用精確匹配

二、VLOOKUP函數(shù)模糊匹配，返回你需要的區(qū)間數(shù)據(jù)

比如根據(jù)考核等級確定獎金比例，對于這種在區(qū)間范圍內(nèi)匹配調(diào)用數(shù)據(jù)，就要用到VLOOKUP函數(shù)的模糊匹配了，這個功能完全可以替代掉IF函數(shù)的多層嵌套，再也不用為寫錯順序發(fā)愁。

操作步驟：查找范圍依然為絕對引用，可按快捷鍵F4，模糊匹配下參數(shù)為1或TRUE。

注意事項：等級表的編制要從小到大

VLOOKUP查詢調(diào)用模糊匹配

說清楚大方向之后，我們來分享一下VLOOKUP的幾個常規(guī)操作方法：

1、常規(guī)查找

查找姓名對應的銷售額。在F3單元格中輸入公式=VLOOKUP(E3,$A$2:$C$9,3,0)，按Enter鍵完成。如下圖所示：

2、日期查找

在查找日期的時候查找的結(jié)果通常會是一串數(shù)字，為了使日期能夠返回相應的格式，那么需要配合TEXT函數(shù)才能完成查找需求。

在F3單元格中輸入公式=TEXT(VLOOKUP(E3,$A$2:$C$9,2,0),'yyyy/m/d')，按Enter鍵完成。如下圖所示：

注：如返回格式為2018/12/03，則TEXT的第二個參數(shù)的格式可以設置為“yyyy/mm/dd”即可。

3、查找的值為空時

在當查找的值為空時，通常情況下會返回結(jié)果為0，那么如果讓結(jié)果返回空白呢，解決的方法就是在公式后面一個“”。

在F3單元格中輸入公式=VLOOKUP(E3,$A$2:$C$9,3,0)&''，按Enter鍵完成。如下圖所示：

4、當查找的目標格式不統(tǒng)一時報錯如何解決

（1）如果查找的目標值是文本格式，而數(shù)據(jù)區(qū)域中是數(shù)值格式。

如下圖所示，A列中的員工編號為數(shù)值格式，而F3單元格中的員工編號為文本格式。

在G3單元格中輸入公式：=VLOOKUP(--F3,$A$2:$D$9,4,0)，按Enter鍵完成。

注：--為兩個負號，即減負的意思，可以理解為負負得正，這里是把文本強制轉(zhuǎn)換為數(shù)值，所以問題就很容易被解決了。

（2）如果查找的目標值是數(shù)值格式，而數(shù)據(jù)區(qū)域中是文本格式。

如下圖所示，A列中的員工編號為文本格式，而F3單元格中的員工編號為數(shù)值格式。

在G3單元格中輸入公式：=VLOOKUP(F3&'',$A$2:$D$9,4,0)，按Enter鍵完成。

注：&''是強制地把數(shù)值格式轉(zhuǎn)換成文本格式。

5、區(qū)域查找

有時候需要查找某一個值處于那個區(qū)間里。比如查找下列的銷售額對應的銷售提點為多少。在E2單元格中輸入公式：=VLOOKUP(D2,$H$2:$I$8,2,1)，按Enter鍵完成。

注：這里使用該函數(shù)最后一個參數(shù)為1，即模糊查找，來確定查找的值處于給定的那一個區(qū)間。

6、模糊查找

VLOOKUP函數(shù)也是支持模糊查找，即支持通配符查找。

查找姓名中帶有“冰”字的員工的銷售額，在H3單元格中輸入公式：

=VLOOKUP('*'&G3&'*',$B$2:$D$9,3,0)，按Enter鍵完成。

注：如果要查找以“冰”開頭的那么公式的第一參數(shù)為：'*'&G3;?如果查找以“冰”結(jié)尾那么公式的第一個參數(shù)為：G3&'*'.

7、查找順序與數(shù)據(jù)區(qū)域中順序一致的多項時

VLOOKUP函數(shù)查找順序一致的多項時，可以借助COLUMN函數(shù)構(gòu)建查找序列。

在H2單元格中輸入公式：=VLOOKUP($G2,$A$2:$D$9,COLUMN(B1),0)，按Enter鍵后向右填充。

注：COLUMN函數(shù)是返回列號。第一個參數(shù)一定要鎖定列號，這樣才能正確的結(jié)果。

8、十字交叉查詢

VLOOKUP函數(shù)如果有兩個條件是呈現(xiàn)十字交叉時且順序與數(shù)據(jù)區(qū)域中的順序不一致時，可以與MATCH函數(shù)完成查詢。

在H2單元格中輸入公式：=VLOOKUP($G2,$A$2:$D$9,MATCH(H$1,$A$1:$D$1,0),0)，按Enter鍵完成后向下向右填充。

注：一定要鎖定VLOOKUP函數(shù)的第一個參數(shù)的列號，MATCH函數(shù)的第一個參數(shù)的行號，這樣才能得到正確的結(jié)果。

9、多條件查詢

VLOOKUP還能進行多條件查詢，這個用法相信有很多人不知道吧。

在I2單元格中輸入公式：

{=VLOOKUP(G2&H2,IF({1,0},$A$2:$A$9&$B$2:$B$9,$D$2:$D$9),2,0)}

按組合鍵完成后向下填充。

注：公式兩邊的花括號不是手動輸入的，而是按組合鍵后自動輸入的。VLOOKUP的第三個參數(shù)為2，第四個參數(shù)為0是固定的。

10、反向查找

VLOOKUP函數(shù)也可以進行反向查找。

在H2單元格中輸入公式：{=VLOOKUP(G2,IF({1,0},$B$2:$B$9,$A$2:$A$9),2,0)}，按組合鍵鍵完成后向下填充。

注：公式兩邊的花括號不是手動輸入的，而是按組合鍵后自動輸入的。

11、一對多查詢

VLOOKUP函數(shù)還能進行一對多查詢，但是這個方法并不鼓勵大家去使用。

在H2單元格中輸入公式：

{=VLOOKUP($G$2&ROW(A1),IF({1,0},$A$2:$A$9&COUNTIF(INDIRECT('a2:a'&ROW($2:$9)),$G$2),$D$2:$D$9),2,0)}，按組合鍵完向下填充。

注：公式兩邊的花括號不是手動輸入的，而是按組合鍵后自動輸入的。

以上就是VLOOKUP的主要應用場景總結(jié)。那么，問題來了！利用率如此之高的函數(shù)為什么會退休？

XLOOKUP比VLOOKUP好在哪？

在微軟的官方的介紹中，XLOOKUP的功能是這樣的：

從右向左查

多條件查找

從上向下查

查找最后一個

.......

（圖片來源于秋葉Excel）

從這張圖中可以看出XLOOKUP擁有著其它函數(shù)無可比擬的優(yōu)勢，當需要在表格或區(qū)域中按行查找項目時, 就可以使用XLOOKUP函數(shù)。?

XLOOKUP廣泛應用后，對于我們財務而言，將可以少學不少很難的數(shù)組公式，VLOOKUP的函數(shù)的各種用法也不用學了！

根據(jù)官方的的解釋，可以簡單理解為：XLOOKUP?可以按照行或者列進行查詢，并返回對應的結(jié)果。

語法：

XLOOKUP 函數(shù)搜索區(qū)域或數(shù)組, 并返回與它找到的第一個匹配項相對應的項。如果不存在匹配項, 則 XLOOKUP 可以返回最接近 (近似) 匹配。?

看著有些復雜，簡單解釋就是：

注意，參數(shù)一共有5個，如果后兩個省略，那么就是精確匹配！

目前該函數(shù)只有部分Office 365預覽版用戶才能使用。

XLOOKUP到底能怎么用？

也許還是有很多人看著糊涂，我們接下來以具體的案例來說明，看看XLOOKUP到底在什么場景下能應用，以及它的強大功能！

一、單條件精確匹配：查找注會菌會計的分數(shù)

=Xlookup(A11,A2:A8,B2:B8)

二、反向精確匹配：從右到左找到注會菌的準考證號碼

=XLOOKUP(A11,B1:B8,A1:A8)

VLOOKUP一般只能從左向右查找，雖然可以實現(xiàn)，但是會復雜很多！用VLOOKUP的公式如下：

=VLOOKUP(A11,IF({1,0},B1:B8,A1:A8),2,FALSE)

簡單地說，XLOOKUP就是把INDEX+MATCH整合在了一起。

三、多條件匹配：查找注會菌的審計分數(shù)

=XLOOKUP(A11&B11,A1:A8&B1:B8,D1:D8)

多項查找也方便了很多。

四、匹配最后一個：查找注會菌最后一次會計模擬考試成績

=XLOOKUP(A14,A2:A11,B2:B11,0,-1)

針對有多個結(jié)果，有時候往往需要匹配最近的一條數(shù)據(jù)。這里只需要將第5參數(shù)改為-1，就能從后向前查了，一般默認為1，是從前向后查找。

如果用VLOOKUP來查找最后一個就非常復雜了，一般可以用LOOKUP構(gòu)造數(shù)據(jù)。

五、橫向精確匹配：按行上下查找注會菌的會計分數(shù)

XLOOKUP用橫向精確查找非常簡單，因為這個函數(shù)沒有橫向和縱向的區(qū)別。

但是，如果用VLOOKUP就不簡單了，可以用HLOOKUP代替或者INDEX+MATCH。

以上就是XLOOKUP的一部分功能，總的來說，它整合了VLOOKUP，HLOOKUP，以及INDEX+MATCH的功能，可以說非常強大。不過注會菌還是要提醒一下大家，目前該函數(shù)只有部分Office 365預覽版用戶才能使用，還沒有全面開放，我們可以一起期待一下。

有人說財務工資的高低和Excel技能的熟練程度有很大的關(guān)系，希望今天這些內(nèi)容能幫助到大家，想看更多干貨內(nèi)容，猛戳右下角“在看”，你們懂的！