Vlookup函數(shù)的使用方法（入門進階高級最新用法）

心理咨詢診所 2020-09-25

展開全文

N年前，蘭色曾寫過一篇Vlookup從入門到高級的全系列教程，被無數(shù)網(wǎng)站轉(zhuǎn)載和抄襲。過了這么多年，Vlookup函數(shù)的新用法又陸續(xù)發(fā)現(xiàn)了不少，所以蘭色覺得有必要再次整理一下這個工作中最常用函數(shù)用法。包含以下內(nèi)容：

一、基本語法

1、參數(shù)詳解

二、入門篇

1、單條件查找

2、屏蔽查找返回的錯誤值

三、進階篇

1、反向查找

2、包含查找

3、區(qū)間查找

4、含通配符查找

5、多列查找

6、多區(qū)域查找

四、高級篇

1、多條件查找

2、合并單元格查找

3、帶合并單元格的多條件查找

4、一對多查找

5、查找所有值放在一個單元格里

6、查找最后一個

7、跨多表查找

五、新版本中的最新用法

1、批量查找

2、多列批量查找

一、基本語法

=Vlookup(查找的值，查找區(qū)域或數(shù)組,返回值所在的列數(shù),精確or匹配查找)

語法說明：

查找的值：要查找的值
查找區(qū)域或數(shù)組：包含查找值字段和返回值的單元格區(qū)域或數(shù)組
返回值的在列數(shù)：返回值在查找區(qū)域的列數(shù)
精確or匹配查找：值為0或False為精確查找，值為1或true時匹配查找。

二、入門篇

1、單條件查找

【例1】根據(jù)姓名查找基本工資

=VLOOKUP(G2,B:E,4,0)

注：

G2：是要查找的值
B:E：是查找區(qū)域。因為要查找的姓名在第2列，所以區(qū)域也要從B列開始。
4：是基本工資在B:E區(qū)域中的第4列
0：是精確查找

2、查找不到時返回空

【例1】根據(jù)姓名查找基本工資

=IFERROR(VLOOKUP(G2,B:E,4,0),'')

注：IFERROR函數(shù)可以把錯誤值轉(zhuǎn)換為指定的值，本例公式中轉(zhuǎn)換為空

三、進階篇

1、反向查找

【例】根據(jù)姓名查部門

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

注：公式中用IF({1,0} 把B列和A列組合在一起，并把 B列放在A列前面。

2、包含查找

【例】查找含“一”的姓名對應的基本工資

=VLOOKUP('*'&G2&'*',B:E,4,0)

注：查找值兩邊連接通配符*號可以實現(xiàn)包含查找

3、區(qū)間查找

【例】根據(jù)銷量查找對應區(qū)間的提成

=VLOOKUP(D2,A:B,2,1)

注：當最后一個參數(shù)為1或省略時，可以實現(xiàn)匹配或區(qū)間查找。規(guī)則是查找比被查找值小且最接近的值，并返回對應N列（第3個參數(shù)）的結(jié)果。如下圖所示查找180，在A列查找比180且最接近的值是100，返回100對應的提成3%。

4、含通配符的查找

【例】型號查找單價

錯誤公式：

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

正確公式：

=VLOOKUP(SUBSTITUTE(D2,'*','~*'),A:B,2,0)

注：把*用函數(shù)替換為~*后就可以正常查找了

5、橫向多列查找

【例】根據(jù)姓名查找性別、年齡和基本工資

=VLOOKUP($G2,$B:$E,COLUMN(B1),0)

注：用Column()函數(shù)生成動態(tài)數(shù)字，作為Vlookup第3個參數(shù)，一個公式向右復制即可查找全部

6、多區(qū)域查找

【例9】根據(jù)不同的表從不同的區(qū)域查詢

=VLOOKUP(B2,IF(A2='銷售一部',A5:B9,D5:E9),2,0)

四、高級篇

1、多條件查找

【例】根據(jù)部門和姓名查工資

=VLOOKUP(E2&F2,IF({1,0},A2:A8&B2:B8,C2:C8),2,0)

注：先把A列和B列連接在一起，再用IF({1,0} 把它和C列組合在一起構(gòu)成8行2列的數(shù)組，作為Vlookup的第2個參數(shù)

2、合并單元格查找

【例】查找所在部門的獎金

=VLOOKUP(VLOOKUP('座',D$2:D2,1),A:B,2,0)

注：VLOOKUP('座',D$2:D2,1)可以返回D列截止本行的最后一個非空值。

3、合并單元格查找

【例】根據(jù)公司、產(chǎn)品查找對應價格

=VLOOKUP(F2,OFFSET(B$1,MATCH(E2,A:A,)-1,):C99,2,0)

注：用Match查找出部門所在行數(shù)，然后用offset函數(shù)向下偏移B1，進爾和C99構(gòu)成一個動態(tài)的區(qū)域。更簡單的說就是部門在哪一行，我就用Vlookup從哪一行開始向下找。

4、一對多查找

【例】查找出人事部所有員工

數(shù)組公式輸入完成后按Ctrl+shift+enter結(jié)束后自動添加大括號

{=VLOOKUP(E$2&ROW(A1),IF({1,0},A$2:A$8&COUNTIF(INDIRECT('a2:a'&ROW($2:$8)),E$2),B$2:B$8),2,0)}

注：

ROW($2:$8)) :生成2,3,4,5,6,7,8
INDIRECT('a2:a'&row : 生成行數(shù)逐漸增多的7個區(qū)域
COUNTIF(INDIRECT : 在7個區(qū)域中分別計算部門的個數(shù)，相當于給人事部生成編號
IF({1,0} : 把帶編號的部門和B列構(gòu)成7行兩列的新數(shù)組

5、查找所有值放在一個單元格

【例】在G列設置公式，根據(jù)F列產(chǎn)品從左表中查找所有符合條件的價格并用逗號隔開。

公式：

E2=D2&','&IFERROR(VLOOKUP(C2,C3:E$12,3,),'')
G2=VLOOKUP(F2,C:E,3,)'

6、查找最后一個

【例】查找A產(chǎn)品最后一次進貨價格

=VLOOKUP(1,IF({100,0},0/(B2:B10='A'),C2:C10),2)

注：Vlookup最后一個參數(shù)省略時，可以象lookup進行二分法查找，用0/（條件）把不符合條件的變成錯誤值，符合條件的變成0，然后用一個足夠大的數(shù)查找。IF后蘭色故意把常見的1寫成100，想讓大家知道這個只要是非0的數(shù)字都可以。

7、跨多表查找

【例】從各部門中查找員工的基本工資，在哪一個表中不一定。

方法1

=IFERROR(VLOOKUP(A2,服務!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),'無此人信息')))))

方法2：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({'銷售';'服務';'人事';'綜合';'財務'}&'!a:a'),A2),{'銷售';'服務';'人事';'綜合';'財務'})&'!a:g'),7,0)