一個(gè)人人都愛、使用頻率最高的函數(shù)，有關(guān)他的用法，你真的掌握了嗎？

語(yǔ)法格式：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（要查找的值，查找區(qū)域，要返回的結(jié)果在查找區(qū)域的第幾列，精確匹配或近似匹配）

1、精確查找

根據(jù)姓名查找對(duì)應(yīng)部門：

輸入公式：=VLOOKUP(G2,A:C,3,0)

G2：要查找的內(nèi)容

A:C：查找區(qū)域，注意查找區(qū)域的首列要包含查找的內(nèi)容

3：要返回的結(jié)果在查找區(qū)域的第3列

0：精確查找

2、近似查找

根據(jù)分?jǐn)?shù)查找對(duì)應(yīng)等級(jí)：

輸入公式：=VLOOKUP(B2,E:F,2,1)

B2：要查找的內(nèi)容

E:F：查找區(qū)域，注意查找區(qū)域的首列要包含查找的內(nèi)容

2：要返回的結(jié)果在查找區(qū)域的第2列

1：近似查找

注意查找區(qū)域中的首列內(nèi)容必須以升序排序。

3、格式不一致的查找

查找數(shù)據(jù)為4的數(shù)量：

輸入公式：=VLOOKUP(D2,A:B,2,0)

D2：要查找的內(nèi)容

A:B：查找區(qū)域，注意查找區(qū)域的首列要包含查找的內(nèi)容

2：要返回的結(jié)果在查找區(qū)域的第2列

0：精確查找

這都沒錯(cuò)啊，為什么結(jié)果會(huì)返回錯(cuò)誤值#N/A呢？

細(xì)看之下你就會(huì)發(fā)現(xiàn)格式不一致

查找值數(shù)值型（D2單元格內(nèi)容4是數(shù)值型）

查找區(qū)域文本型（A列的數(shù)據(jù)是文本型）

遇到這樣的問題該怎么解決呢？

格式一致

一是可以利用分列功能將A列分列成常規(guī)，與D2單元格格式一致

二是可以將D2單元格內(nèi)容設(shè)成文本格式，與A列格式一致

三是變公式

公式：=VLOOKUP(D2&'',A:B,2,0)

將查找值連接空（&''）變?yōu)槲谋?/p>

接下來(lái)順便說(shuō)下另一種格式不一致問題：

查找值文本型，查找區(qū)域數(shù)值型

查找值文本型（D2單元格內(nèi)容4是文本型）

查找區(qū)域數(shù)值型（A列的數(shù)據(jù)是數(shù)值型）

輸入公式：

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

^1是將查找值轉(zhuǎn)換成和查找區(qū)域一致的格式

轉(zhuǎn)換方法多種：--、+0、-0、*1、/1...等等

4、通配符查找

根據(jù)簡(jiǎn)稱查找對(duì)應(yīng)應(yīng)收賬款：

輸入公式：

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

星號(hào)（*）匹配任意一串字符。

5、帶“~”的查找

根據(jù)姓名查找對(duì)應(yīng)部門：

公式?jīng)]有錯(cuò)，結(jié)果為什么會(huì)返回錯(cuò)誤值#N/A呢？

因?yàn)椴檎覂?nèi)容帶波形符（~）

輸入公式：

=VLOOKUP(SUBSTITUTE(G2,'~','~~'),A:C,3,0)

在查找包含通配符其本身內(nèi)容時(shí)，需在通配符前鍵入“~”

用函數(shù)SUBSTITUTE將“~”替換成“~~”。

6、取消合并單元格

內(nèi)容為數(shù)值，取消合并單元格：

輸入公式：

=VLOOKUP(9E+307,A$2:A2,1,1)

9E+307是科學(xué)記數(shù)，表示9*10^307，是Excel允許鍵入的最大數(shù)值。

內(nèi)容為文本，取消合并單元格：

輸入公式：

=VLOOKUP('座',E$2:E2,1,1)

7、查找第一次價(jià)格

根據(jù)物料名稱查找對(duì)應(yīng)第一次價(jià)格：

輸入公式：

=VLOOKUP(F2,B:D,3,0)

當(dāng)查找區(qū)域首列出現(xiàn)有兩個(gè)或更多值與查找值匹配時(shí)，函數(shù)VLOOKUP返回第一次出現(xiàn)的對(duì)應(yīng)值。

8、交叉查詢

根據(jù)產(chǎn)品和地區(qū)查找對(duì)應(yīng)銷量：

輸入公式：

=VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0)

MATCH(B12,A1:G1,0)部分找到B12單元格內(nèi)容“華北地區(qū)”在區(qū)域A1:G1中的位置5，把它作為VLOOKUP函數(shù)的第3參數(shù)；

公式就是：=VLOOKUP(A12,A2:G8,5,0)

查找A12單元格內(nèi)容“產(chǎn)品D”

返回值在區(qū)域A2:G8中的第5列，即E列

即E5單元格中的值6945

9、反向查找

根據(jù)工號(hào)查找對(duì)應(yīng)姓名：

函數(shù)VLOOKUP可以借助IF{1,0}與IF{0,1}、CHOOSE{1,2}與CHOOSE{2,1}等等結(jié)構(gòu)將逆序轉(zhuǎn)換為順序，從而實(shí)現(xiàn)查找。

函數(shù)VLOOKUP+ IF{1,0}結(jié)構(gòu)：

輸入公式：

=VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0)

IF({1,0},B2:B11,A2:A11)部分

當(dāng)為1時(shí)條件成立返回B2:B11

當(dāng)為0時(shí)條件不成立返回A2:A11

可以將IF({1,0},B2:B11,A2:A11)部分抹黑按F9鍵查看

就是兩列順序?qū)Q，將逆序轉(zhuǎn)換為順序

函數(shù)VLOOKUP+ IF{0,1}結(jié)構(gòu)：

輸入公式：

=VLOOKUP(D2,IF({0,1},A2:A11,B2:B11),2,0)

函數(shù)VLOOKUP+CHOOSE{1,2}結(jié)構(gòu)：

輸入公式：

=VLOOKUP(D2,CHOOSE({1,2},B2:B11,A2:A11),2,0)

函數(shù)CHOOSE：根據(jù)給定的索引值，從參數(shù)串中選出相應(yīng)值或操作。

CHOOSE(index_num, value1, [value2], ...)

如果第一參數(shù)為1，則CHOOSE返回value1；如果第一參數(shù)為2，則CHOOSE返回value2。

CHOOSE({1,2},B2:B11,A2:A11)部分

當(dāng)條件為1時(shí)，返回B2:B11

當(dāng)條件為2時(shí)，返回A2:A11

函數(shù)VLOOKUP+CHOOSE{2,1}結(jié)構(gòu)：

輸入公式：

=VLOOKUP(D2,CHOOSE({2,1},A2:A11,B2:B11),2,0)

CHOOSE({2,1},A2:A11,B2:B11)部分

當(dāng)?shù)谝粎?shù)為2時(shí)，則CHOOSE返回對(duì)應(yīng)B2:B11中的值；

當(dāng)?shù)谝粎?shù)為1時(shí)，則CHOOSE返回對(duì)應(yīng)A2:A11中的值。

把CHOOSE({2,1},A2:A11,B2:B11)部分抹黑按F9鍵查看

AB兩列順序?qū)Q，將逆序轉(zhuǎn)換為順序，再用函數(shù)VLOOKUP查找。

10、查找返回多列數(shù)據(jù)

輸入公式：

=VLOOKUP($F2,$A:$D,COLUMN(B1),0)，右拉填充

公式右拉返回結(jié)果在第2、3、4列

用函數(shù)COLUMN構(gòu)造

COLUMN(B1)=2，公式右拉變成COLUMN(C1)、COLUMN(D1)得到3、4。

11、按指定次數(shù)重復(fù)

輸入公式：

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,ROW($1:$4)),'<>'),A$2:A$5),2,0),E2)&''

按<Ctrl+Shift+Enter>三鍵結(jié)束

12、結(jié)果引用合并單元格內(nèi)容

A列區(qū)域?yàn)楹喜卧?，根?jù)業(yè)務(wù)員查找對(duì)應(yīng)的區(qū)域：

輸入公式：

=VLOOKUP('座',OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1)

MATCH(D2,B2:B14,0)部分找到業(yè)務(wù)員“阿文”在區(qū)域B2:B14中的位置11

OFFSET（基點(diǎn)，偏移行數(shù)，偏移列數(shù)，行高，列寬）

OFFSET(A2,,,11)是以A2單元格為基點(diǎn)，偏移0行0列，返回行高為11的新區(qū)域A2:A12的引用。

OFFSET部分抹黑按F9鍵得到：

用“座”等較大的漢字查找區(qū)域中最后一個(gè)單元格內(nèi)容，即返回“華北地區(qū)”。

13、有合并單元格的查找

A列產(chǎn)品為合并單元格，如何查找A列產(chǎn)品對(duì)應(yīng)的單價(jià)呢？

輸入公式：

=VLOOKUP(VLOOKUP('座',A$2:A2,1,1),F:G,2,0)

比如D5單元格公式=VLOOKUP(VLOOKUP('座',A$2:A5,1,1),F:G,2,0)

A$2:A5部分返回{'產(chǎn)品1';'產(chǎn)品3';0;0}

VLOOKUP('座',A$2:A5,1,1)部分用'座'查找最后一個(gè)單元格內(nèi)容，即返回“產(chǎn)品3”

外層再套個(gè)VLOOKUP精確查找

即D5單元格公式就是=VLOOKUP('產(chǎn)品3',F:G,2,0)，返回單價(jià)12

14、與T+IF的組合應(yīng)用

輸入公式：

=SUM(VLOOKUP(T(IF({1},A2:A8)),D2:E8,2,0)*B2:B8)

數(shù)組公式，按<Ctrl+Shift+Enter>三鍵結(jié)束

IF({1},A2:A8)部分構(gòu)成三維內(nèi)存數(shù)組

VLOOKUP函數(shù)第一參數(shù)不能直接為數(shù)組

函數(shù)T起降維作用，將三維引用轉(zhuǎn)換為一維數(shù)組，其返回的結(jié)果仍為數(shù)組，用函數(shù)SUM求和。

15、多條件查找

與反向查找一樣，可以借助IF{1,0}與IF{0,1}、CHOOSE{1,2}與CHOOSE{2,1}等結(jié)構(gòu)

輸入公式：

=VLOOKUP(E2&F2,IF({1,0},A2:A11&B2:B11,C2:C11),2,0)

數(shù)組公式，按<Ctrl+Shift+Enter>三鍵結(jié)束

16、一對(duì)多查找

輸入公式：

=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$A$2:$A$11&COUNTIF(INDIRECT('A2:A'&ROW($2:$11)),$E$2),B$2:B$11),2,),'')

數(shù)組公式，按<Ctrl+Shift+Enter>三鍵結(jié)束

效果圖：

17、動(dòng)態(tài)圖表

【數(shù)據(jù)】→【數(shù)據(jù)驗(yàn)證】

輸入公式：

=VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0) ，右拉填充

【插入】→【插入柱形圖】

操作演示：