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

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

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

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

語(yǔ)法結(jié)構(gòu)：

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

解讀：

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

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

具體的語(yǔ)法說(shuō)明大家按F1看幫助文檔吧，本文就不再詳細(xì)解讀了。

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

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

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

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

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

表中黃色區(qū)域?yàn)楣剿谖恢?，以E2單元格公式為例

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

這個(gè)公式是標(biāo)準(zhǔn)的VLOOKUP函數(shù)的基礎(chǔ)應(yīng)用方法，每個(gè)參數(shù)都沒(méi)有變形，所以很方便初學(xué)者獲悉這個(gè)函數(shù)最原始的含義和作用。

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

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

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

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

翻譯過(guò)來(lái)就是

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

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

如果有多個(gè)條件要同時(shí)滿足怎么辦?

其實(shí)很簡(jiǎn)單，可以在數(shù)據(jù)源左側(cè)創(chuàng)建一個(gè)輔助列，將多個(gè)條件用&符號(hào)連接起來(lái)作為條件查找列。

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

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

表中黃色區(qū)域?yàn)楣剿谖恢茫訥2單元格公式為例

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

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

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

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

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

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

數(shù)組公式是為了應(yīng)對(duì)一些比較復(fù)雜的計(jì)算，需要對(duì)一組或者多組數(shù)據(jù)執(zhí)行多項(xiàng)計(jì)算，返回一個(gè)值或者一組值的公式，這樣的公式輸入完成后需要按<Ctrl+Shfit+Enter>組合鍵結(jié)束輸入，公式兩側(cè)會(huì)自動(dòng)出現(xiàn)一對(duì)大括號(hào){}，標(biāo)識(shí)這個(gè)公式是要按照數(shù)組運(yùn)算模式來(lái)計(jì)算的。

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

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

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

這個(gè)案例告訴你答案。

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

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

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

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

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

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

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

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

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

想的沒(méi)錯(cuò)，這樣是可以實(shí)現(xiàn)的，但以下幾種情況下還是學(xué)會(huì)一個(gè)公式一步到位的比較好：

1、當(dāng)數(shù)據(jù)源格式不允許改動(dòng)時(shí)

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

3、當(dāng)你想除了基礎(chǔ)用法之外，多學(xué)點(diǎn)高逼格用法時(shí)，哈哈

所以說(shuō)技多不壓身，多學(xué)一手，何樂(lè)而不為呢?

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

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

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

工作中除了精確查找外，模糊匹配也經(jīng)常遇到。結(jié)合下面這個(gè)案例來(lái)學(xué)習(xí)模糊匹配技巧。

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

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

[0,60): D

[60,80): C

[80,90): B

[90,100]: A

看到這里，很多小伙伴一定想到用IF多條件嵌套來(lái)解決，沒(méi)錯(cuò)，使用IF是可以實(shí)現(xiàn)的，比如這兩個(gè)公式都能實(shí)現(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")))

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

有沒(méi)有更簡(jiǎn)便的辦法呢?當(dāng)然，只要學(xué)會(huì)VLOOKUP模糊匹配技巧就可以了。

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

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

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

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

下面結(jié)合一個(gè)案例來(lái)介紹。

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

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

E2公式為：

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

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

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

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

當(dāng)消費(fèi)金額處在兩級(jí)會(huì)員等級(jí)之間時(shí)，按較低一級(jí)的等級(jí)算，比如消費(fèi)金額3333，處于會(huì)員等級(jí)三級(jí)和四級(jí)之間，那么該會(huì)員屬于三級(jí)會(huì)員，只有達(dá)到5000消費(fèi)金額后才算四級(jí)會(huì)員。

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

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

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

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

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

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

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

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

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

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

結(jié)合案例來(lái)看。

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

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

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

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

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

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

=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ù)在合并單元格中查找

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

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

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

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

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

G2輸入以下公式

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

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

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

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

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

=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ù)行列結(jié)構(gòu)

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

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

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

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

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

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