VLOOKUP函數高難度實戰用法

VLOOKUP函數是查找函數，常用於從大量數據或信息中查找指定的值。是Excel函數中使用頻率最高的函數之一，很多用戶用自身的親身經歷見證了Vlookup函數在提高工作效率的神奇；以致於大家紛紛奔走相告，使得Vlookup函數幾乎成了Excel函數的代名詞。

那我們一起來看看Vlookup函數有哪些實戰應用技巧！

（分起步、進階、高級三部分）

基礎語法:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

通俗理解：

VLOOKUP(查找目標,查找範圍,返回值的列數,精確查找OR模糊查找)

VLOOKUP起步應用：

下面以一個實例來介紹這四個參數的用法。

如下圖所示，根據表2中的物料編號，查找物料編號對應的價格。

簡要解析:

查找目標：要找誰，在此我們需要查找的內容是B16；

查找範圍：在哪找， 一般在一個區域中找，這個區域要同時滿足2個條件:

第一: 查找目標一定要位於區域中第1列，本例中物料編號在表1中是第二列（B列），所以查找區域從第二列開始，即B列，而不是A列。

第二: 該區域一定要包含要返回值所在的列，本例中要返回的是價格，所以查找區域中要包含E列，在實際使用時價格所在列一般是最後一列。

綜合所述，查找範圍是$B$3:$E$12，不是$A$3:$E$12。

返回值的列數：往哪找，本例中要返回的是價格，價格在查找範圍$B$3:$E$12中從左往右數是第4列，而不是工作表中的第5列。

精確查找OR模糊查找： 怎麼找，0或FALSE代表精確查找，1 或TRUE代表模糊查找，一般使用精確查找的比較多。

VLOOKUP進階應用：

我們仍以表1作為數據源，根據編號來查找名稱、供應商和價格

場景1：要查找的名稱、供應商和價格順序與表1標題順序相同時

公式依次如下:

名 稱: =VLOOKUP(B21,$B$3:$E$12,2,0)

供應商:=VLOOKUP(B21,$B$3:$E$12,3,0)

價 格: =VLOOKUP(B21,$B$3:$E$12,4,0)

上述三個公式除第3個參數」返回值的列數」不同外，其它均相同，這樣一個個修改起來比較麻煩，那能不能讓公式往後複製時第3個參數能自動變為2、3、4、……呢？

此時，我們需要用到一個COLUMN函數，它可以返回指定單元格所在的列數，

比如:

=COLUMN（B1） 返回值2

=COLUMN（C1） 返回值3=COLUMN（D1） 返回值4

所以C21單元格中完整的公式是:

=VLOOKUP($B21,$B$3:$E$12,COLUMN(B1),0) 再向後複製即可

場景2：要查找的名稱、供應商和價格順序與表1標題順序不同時

再用COLUMN就不適合了，COLUMN只能按順序生成序列號；

這時需要用到另一個MATCH函數，它可以算出名稱、供應商和價格在表1中所在的位置，並返回正確的值。

MATCH基礎語法:

MATCH(lookup_value, lookup_array, [match_type])

MATCH通俗理解:

MATCH(查找目標, 查找範圍, 精確查找OR模糊查找), 如:

（MATCH基本語法與VLOOKUP類似，在此就不作重點講解）

所以C21單元格中完整的公式是:

=VLOOKUP($B21,$B$3:$E$12,MATCH(C20,$B$2:$E$2,0),0) 再向後複製即可

上述由VLOOKUP+MATCH構成的嵌套函數對於剛接觸函數的人來說比較難，也容易寫錯，可以用以下的方法來寫。

VLOOKUP高級應用： 場景1:模糊查找，與通配符【*】和連接符【&】的聯合應用

如下圖所示，根據表2中的單位簡稱，在表1中找到對應單位的銷售金額。

上述案例中在E3單元格中輸入:

=VLOOKUP(E3,$A$3:$B$10,2,0)會返回錯誤值#N/A.

這是因為查找目標＂單位簡稱＂與＂單位全稱＂對應的單位是不同的（如長沙麗景與長沙麗景汽車銷售有限公司雖然是單位簡稱與全稱的區別，但對於Excel來說，是不同的文本）。

在此情況下，我們需要用到通配符【*】和連接符【&】

在E3中輸入:

=VLOOKUP("*"&E3&"*",$A$3:$B$10,2,0),往下複製。

解釋:【*】作為文本對待，文本與文本之間是不能直接連接的，需要通過連接符【&】實現。

另【*】代表多個字元，【?】代表單個字元。

本例中單位簡稱與全稱相比，前後字元數不同，所以用的是【*】。

場景2：指定一個數就可以查找出它落在哪個區間並返回這個區間所對應的值。

如下圖所示，表1是不同任務完成率區間對應不同獎勵點數，現需要把表2中不同單位的獎勵點數算出來。先構建一個輔助區域，如表3

在F3中輸入: =VLOOKUP(E3,$A$11:$B$15,2) 向下複製即可。

這在本案例中VLOOKUP第四個參數如果是1或TRUE或省略，則實現區間查找正是第4個參數的模糊查找應用。

模糊查找有兩個非常重要的原則：

第一：引用的數字區域一定要從小到大排序。雜亂的數字是無法準確查找到的。如下面A列符合模糊查找的前題，B列則不符合。

第二：給定一個數，它會找到和它最接近，但比它小的那個數。

上案中，89.9%非常接近90%，但取比90%小的那個數，所以獎勵點數是4%，而不是5%。

場景3：反向查找，從右往左查找。

正常情況下，VLOOKUP函數只能從左往右查找，要實現從右往左查找，需要通過一定的方法重新構建第二個參數「查找範圍」的區域。

下面通過案例演示:

在C14中輸入公式:

=VLOOKUP(B14,CHOOSE({2,1},$B$2:$B$11,$C$2:$C$11),2,0)

公式簡述：

本案例中CHOOSE({2,1},$B$2:$B$11,$C$2:$C$11)是最關鍵的組成部分。

CHOOSE中使用了數組，返回的結果也會是一個數組，在此用2和1把CHOOSE中的區域1（$B$2:$B$11）,區域2（$C$2:$C$11）進行了位置交換；最終形成了{"457橋組合螺母","31030704E";"車架總成","TU3421WW280";……}這樣一個區域。

錯誤值處理：

當查找目標在查找範圍中找不到的時候，就會出現#N/A值，在實際應用中會影響報表的可讀性，我們一般可以把錯誤值轉換成0或空值。

在Excel2007版本（含）以上，可使用IFERROR函數:

= IFERROR（VLOOKUP（參數略），"")

在Excel2003版本，需要用到IF和ISERROR函數，：

=IF(ISERROR(VLOOKUP（參數略）),"",VLOOKUP（參數略）

微信名：Lucifer的辦公室微信名：luciferoffice
推薦閱讀：