Vlookup函數(shù)的使用技巧及應用

1、VLOOKUP函數(shù)語法解析

VLOOKUP 基礎用法是搜索某個單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格中的值。

VLOOKUP 中的 V 表示垂直方向。當比較值位于所需查找的數(shù)據(jù)的左邊一列時，可以使用VLOOKUP

語法結構：

VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])

解讀：

VLOOKUP(找什么，在哪找，找到后返回其右側對應的第幾列數(shù)據(jù)，精確還是模糊查找)

這樣一看是不是清晰多了?

具體的語法說明大家按F1看幫助文檔吧，本文就不再詳細解讀了。

需要說明的一點是，Excel中的幫助信息也有錯誤，比如在插入函數(shù)功能中VLOOKUP第四參數(shù)的說明就是錯的，大家注意不要被誤導哦!如下圖：

紅框部分幫助錯誤，應改為：如果為FALSE或0，精確匹配，如果為TRUE或忽略，大致匹配。

2、VLOOKUP函數(shù)單條件查找

根據(jù)單條件進行數(shù)據(jù)查找是最基礎也是最常見的需求了。

看如下案例，工作中的數(shù)據(jù)源為A:B兩列，分別放置業(yè)務員姓名和對應的銷售額，當需要按照業(yè)務員查找其對應的銷售額時，就要用到VLOOKUP函數(shù)了。

表中黃色區(qū)域為公式所在位置，以E2單元格公式為例

=VLOOKUP(D2,$A$2:$B$12,2,0)

這個公式是標準的VLOOKUP函數(shù)的基礎應用方法，每個參數(shù)都沒有變形，所以很方便初學者獲悉這個函數(shù)最原始的含義和作用。

第一參數(shù)：找什么(或者說按什么查找)，按業(yè)務員查找，所以輸入D2

第二參數(shù)：在哪找，數(shù)據(jù)源區(qū)域在A:B列，所以輸入$A$2:$B$12

第三參數(shù)：找到后返回第幾列，我們要查找的是銷售額，銷售額位于B列，即第二參數(shù)中的第二列，所以輸入2

第四參數(shù)：這里要精確查找，所以輸入0

翻譯過來就是

=VLOOKUP(要查找的業(yè)務員,包含業(yè)務員和其銷售額的數(shù)據(jù)源區(qū)域,找到后返回第2列,精確查找)

3、 VLOOKUP函數(shù)多條件查找

如果有多個條件要同時滿足怎么辦?

其實很簡單，可以在數(shù)據(jù)源左側創(chuàng)建一個輔助列，將多個條件用&符號連接起來作為條件查找列。

如果數(shù)據(jù)源左側不允許插入列，或者想直接用一個公式搞定多條件查找，自然也有辦法啦，下面結合一個案例來介紹這種方法。

看如下案例，工作中的數(shù)據(jù)源為A:C兩列，分別放置水果、產(chǎn)地和對應的銷售額，當需要同時按照水果和產(chǎn)地查找其對應的銷售額時，就要用到VLOOKUP函數(shù)的多條件查找技巧了。

表中黃色區(qū)域為公式所在位置，以G2單元格公式為例

輸入以下數(shù)組公式，按<Ctrl+Shfit+Enter>組合鍵結束輸入。

=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$12&$B$2:$B$12,$C$2:$C$12),2,0)

注意：這個公式是數(shù)組公式，如果直接按Enter鍵輸入會返回#N/A錯誤值。

新人一定搞不懂啥叫數(shù)組公式呢?這里科普一下吧。

Excel中的公式分普通公式和數(shù)組公式。

普通公式不多說啦就是大家最常用的，輸入公式后直接按Enter結束輸入。

數(shù)組公式是為了應對一些比較復雜的計算，需要對一組或者多組數(shù)據(jù)執(zhí)行多項計算，返回一個值或者一組值的公式，這樣的公式輸入完成后需要按<Ctrl+Shfit+Enter>組合鍵結束輸入，公式兩側會自動出現(xiàn)一對大括號{}，標識這個公式是要按照數(shù)組運算模式來計算的。

當鼠標定位在公式所在單元格進入編輯狀態(tài)時，大括號就消失了，當我們按ESC鍵退出編輯狀態(tài)時大括號就又重新出現(xiàn)了。

4、VLOOKUP函數(shù)查找返回多列數(shù)據(jù)

單條件查找會了，多條件查找也學了，都是輸入一個公式然后向下復制填充一列的形式，如果有多列數(shù)據(jù)需要根據(jù)查找值調(diào)取對應數(shù)據(jù)，那么如何輸入一個公式就能搞定一個區(qū)域的數(shù)據(jù)調(diào)用呢?

這個案例告訴你答案。

下圖中左側表格是數(shù)據(jù)源區(qū)域，需要在右側區(qū)域根據(jù)業(yè)務員姓名調(diào)取對應的科目成績，黃色區(qū)域是需要填寫公式的區(qū)域。

這個案例中，觀察到右側的科目順序和數(shù)據(jù)源一致，都是從數(shù)學到體育，如果用最笨的方法一列一列寫公式固然可以實現(xiàn)目的，但當需要查找的列很多時無疑是一項大工程。

這里給出一個簡單實用的公式，選中H2:K5單元格區(qū)域，輸入以下公式后按<Ctrl+Enter>組合鍵。

注意是組合鍵同時按下，而不要只按Enter鍵哦!

=VLOOKUP($G2,$A$2:$E$12,COLUMN(B1),0)

5、VLOOKUP函數(shù)從右向左查找

工作中免不了遇到數(shù)據(jù)源中要調(diào)取的數(shù)據(jù)在查找值所在列的左側，知道VLOOKUP函數(shù)的常規(guī)用法是從左往右找，那么遇到這種需要從右往左查找的問題如何解決呢?

下面的表格中，需要按照給出的編號查找對應的業(yè)務員姓名，黃色區(qū)域輸入公式。

看到這個場景，有的小伙伴或許說，雖然VLOOKUP函數(shù)只能從左往右找，那干脆把數(shù)據(jù)源里面的編號列改到左邊不就行了嗎?

想的沒錯，這樣是可以實現(xiàn)的，但以下幾種情況下還是學會一個公式一步到位的比較好：

1、當數(shù)據(jù)源格式不允許改動時

2、當這項工作出現(xiàn)的頻率較高，你懶得每次都要重新改數(shù)據(jù)源，想一勞永逸時

3、當你想除了基礎用法之外，多學點高逼格用法時，哈哈

所以說技多不壓身，多學一手，何樂而不為呢?

F2單元格輸入以下公式后向下復制填充。

=VLOOKUP(E2,IF({1,0},$B$2:$B$12,$A$2:$A$12),2,0)

6、VLOOKUP函數(shù)按數(shù)據(jù)所處區(qū)間劃分等級

工作中除了精確查找外，模糊匹配也經(jīng)常遇到。結合下面這個案例來學習模糊匹配技巧。

下面表格中左側是數(shù)據(jù)源區(qū)域，需要按照等級劃分規(guī)則，將成績劃分到其對應的等級中。

等級劃分規(guī)則如下：

[0,60): D

[60,80): C

[80,90): B

[90,100]: A

看到這里，很多小伙伴一定想到用IF多條件嵌套來解決，沒錯，使用IF是可以實現(xiàn)的，比如這兩個公式都能實現(xiàn)我們的需求。

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=60,"C","D")))

=IF(B2<60,"D",IF(B2<80,"C",IF(B2<90,"B","A")))

但是當劃分規(guī)則更多時，編輯公式的時候需要一層一層的嵌套，用IF書寫公式簡直變成了體力活。

有沒有更簡便的辦法呢?當然，只要學會VLOOKUP模糊匹配技巧就可以了。

用以下公式就可以實現(xiàn)IF多層條件嵌套同樣的結果了。

=VLOOKUP(B2,{0,"D";60,"C";80,"B";90,"A"},2)

7、VLOOKUP函數(shù)使用通配符模糊查找

當在工作中遇到需要只根據(jù)查找值的一部分進行查找時，記得可以利用通配符的特性來實現(xiàn)。

下面結合一個案例來介紹。

表格中左側是數(shù)據(jù)源，需要查找業(yè)務員名字中帶“強”的人的銷售額。

通配符星號*通配任意個字符，問號?通配單一字符，這個案例中模糊查找的規(guī)則是只要名字中帶“強”就可以，所以我們需要使用“*強*”這種形式，支持“強”字出現(xiàn)在任意位置。

E2公式為：

=VLOOKUP("*強*",$A$2:$B$12,2,0)

8、VLOOKUP函數(shù)多層級條件嵌套查找

遇到多層級條件嵌套查找，很多人第一時間想到的是IF多條件嵌套，還有些高手想到的是LOOKUP函數(shù)查找，其實VLOOKUP函數(shù)也可以搞定。

比如下面這個案例，要根據(jù)會員的消費金額查找其所處的會員等級。

當消費金額處在兩級會員等級之間時，按較低一級的等級算，比如消費金額3333，處于會員等級三級和四級之間，那么該會員屬于三級會員，只有達到5000消費金額后才算四級會員。

E2輸入以下公式，向下填充。

=VLOOKUP(D2,$A$2:$B$8,2)

9、VLOOKUP函數(shù)按指定次數(shù)重復數(shù)據(jù)

工作中一些復雜場景會遇到按指定次數(shù)重復數(shù)據(jù)的需求，如下圖所示。

D列黃色區(qū)域是由公式自動生成的重復數(shù)據(jù)，當左側的數(shù)據(jù)源變動時，D列會按照指定的重復次數(shù)自動更新。

這里使用的是一個數(shù)組公式，以D2為例，輸入以下數(shù)組公式后按<Ctrl+Shfit+Enter>結束輸入。

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUBTOTAL(9,OFFSET(A$2,,,ROW($1:$3))),B$2:B$4),2,),D3)&""

這個思路和方法都很贊，轉(zhuǎn)給朋友們分享一下吧~

10、VLOOKUP函數(shù)返回查找到的多個值

都知道VLOOKUP的常規(guī)用法下，當有多個查找值滿足條件時，只會返回從上往下找到的第一個值，那么如果需要VLOOKUP函數(shù)一對多查找時，返回查找到的多個值，有辦法實現(xiàn)嗎?答案是肯定的。

結合案例來看。

下面表格中左側是數(shù)據(jù)源，當右側D2單元格選擇不同的著作時，需要黃色區(qū)域返回根據(jù)D2查找到的多個值。

在這里，先給出遇到這種情況最常用的一個數(shù)組公式

E2單元格輸入以下數(shù)組公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&""

這是經(jīng)典的一對多查找時使用的INDEX+SMALL+IF組合。

用VLOOKUP函數(shù)的公式，我也給出，E2輸入數(shù)組公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=IF(COUNTIF(A$2:A$11,D$2)<ROW(A1),"",VLOOKUP(D$2&ROW(A1),IF({1,0},A$2:A$11&COUNTIF(INDIRECT("A2:A"&ROW($2:$11)),A$2:A$11),B$2:B$11),2,))

11、VLOOKUP函數(shù)在合并單元格中查找

合并單元格，這個東東大家在工作中太常見了吧。

在工作中盡量避免合并單元格，尤其是在數(shù)據(jù)處理過程中。但這并不能避免跟合并單元格打交道，因為數(shù)據(jù)源來自的渠道太多了，遇到了合并單元格也不能影響到數(shù)據(jù)處理和分析過程。

下面結合一個案例，介紹合并單元格中如何使用VLOOKUP函數(shù)查找。

注意到左側的班級列包含多個合并單元格且都是3行一合并，右側的查找是根據(jù)班級和名次進行雙條件查找。注意是從合并單元格中查找哦。

最簡便的辦法是在數(shù)據(jù)源左側做個輔助列，將合并單元格拆分并填充，這就回歸到前面介紹過的多條件查找的用法了。這個案例不創(chuàng)建輔助列，也不改動數(shù)據(jù)源結構，直接使用公式進行數(shù)據(jù)提取。

G2輸入以下公式

=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,),,3),2,)

12、VLOOKUP函數(shù)提取字符串中的數(shù)值

工作中有時會遇到從一串文本和數(shù)值混雜的字符串中提取數(shù)值的需求，如果字符串比較多而且經(jīng)常變動，與其每次都手動提取數(shù)值，就不如寫好一個公式實現(xiàn)自動提取。當數(shù)據(jù)源更新時，公式結果還能自動刷新。

下面的案例中，可以看到字符串中包含的數(shù)值各式各樣，有整數(shù)也有一位小數(shù)、兩位和多位小數(shù)，還有百分比數(shù)值，使用公式都可以一次性批量提取(百分號提取出來默認按照小數(shù)形式顯示，可以設置格式改變顯示方式)。

首先給出數(shù)組公式，在B2輸入以下數(shù)組，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=VLOOKUP(9E+307,MID(A2,MIN(IF(ISNUMBER(--MID(A2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)

13、VLOOKUP函數(shù)轉(zhuǎn)換數(shù)據(jù)行列結構

VLOOKUP函數(shù)不光能查找調(diào)用數(shù)據(jù)，還可以用來轉(zhuǎn)換數(shù)據(jù)源的布局，比如將行數(shù)據(jù)轉(zhuǎn)換為多行多列的區(qū)域數(shù)據(jù)，如下面案例。

數(shù)據(jù)源位于第二行，要將這個1行20列的行數(shù)據(jù)轉(zhuǎn)換為黃色區(qū)域所示的4行5列的布局。

選中P5:T8單元格區(qū)域，輸入以下區(qū)域數(shù)組公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=VLOOKUP("*",$A$2:$T$2,((ROW(1:4)-1)*5+COLUMN(A:E)),0)

14、VLOOKUP函數(shù)疑難解答提示

在使用VLOOKUP函數(shù)的過程中，很容易遭遇公式返回錯誤值的困境，下面這些錯誤值總結了最常見的問題，介紹產(chǎn)生錯誤原因的同時還給出了排除錯誤值的方法。