在日常工作中，總是會(huì)用到vlookup函數(shù)，今天和大家分享3大類18種vlookup函數(shù)的經(jīng)典用法，從基礎(chǔ)用法到高階用法，幫助大家提高處理數(shù)據(jù)的效率。

基礎(chǔ)用法

語(yǔ)法：VLOOKUP(查找值，數(shù)據(jù)表，序列數(shù)，匹配條件）

語(yǔ)法說(shuō)明：

【查找值】按照某個(gè)值來(lái)查找想要的數(shù)據(jù)，即查找的對(duì)象。

【數(shù)據(jù)表】在什么范圍內(nèi)查找，即查找的區(qū)域，需要按F4絕對(duì)引用。

【序列數(shù)】匹配數(shù)據(jù)在查找區(qū)域的第幾列，第幾列指的是查找區(qū)域內(nèi)想要返回的值所在的列。

【匹配條件】0或FALSE代表精準(zhǔn)匹配，1或TRUE代表近似匹配。

1、精準(zhǔn)查找

公式：

=VLOOKUP(G17,$C$16:$E$21,3,FALSE)

根據(jù)貨品類型來(lái)查找對(duì)應(yīng)的單價(jià)和數(shù)量，如下圖：

需要注意的是C16:E21查找區(qū)域需要用$符號(hào)絕對(duì)引用定位，否則當(dāng)下拉公式的時(shí)候，查找范圍會(huì)跟隨向下移動(dòng)，最終會(huì)導(dǎo)致匹配數(shù)據(jù)出錯(cuò)，如下圖：

2、近似匹配

公式：

=VLOOKUP(H27,$C$26:$D$31,2,TRUE)

近似匹配適合用在區(qū)間查找，查找銷售額在某個(gè)區(qū)間的提成比例，匹配條件用的TRUE，如下圖：

3、反向查找

當(dāng)我們查找的對(duì)象所在列，在被查找的值所在列右邊，我們可以把查找的列移動(dòng)，然后再進(jìn)行查找，即就是反向查找，如下圖：

4、多條件查找

連接列公式：=C48&D48

公式：

=VLOOKUP(H48&I48,$B$47:$F$52,5,FALSE)

根據(jù)水果類型和產(chǎn)地查找市場(chǎng)價(jià)，可以添加輔助列，用連接符&把查找區(qū)域的水果列和產(chǎn)地列連接，然后再查找，如下圖：

嵌套進(jìn)階用法

1、屏蔽錯(cuò)誤值（IFERROR）

當(dāng)查找對(duì)象在查找范圍內(nèi)找不到，就會(huì)報(bào)錯(cuò)，如下圖：

有時(shí)候我們需要把這種查不到從而報(bào)錯(cuò)的項(xiàng)，顯示為空白單元格，或者其它的內(nèi)容，可以用IFERROR函數(shù)和vlookup函數(shù)嵌套使用達(dá)到效果，如下圖：

公式：

=IFERROR(VLOOKUP(F8,$B$6:$D$11,3,0),"")

2、關(guān)鍵詞查找

關(guān)鍵詞查找，也叫模糊查找，就是查找對(duì)象內(nèi)容不完整在查找區(qū)域里只有一部分，比如下圖中查找對(duì)象“合作客戶”只是查找區(qū)域里的“合作客戶”中的一部分內(nèi)容。

關(guān)鍵詞查找一般要用到通配符，*代表匹配0個(gè)或多個(gè)字符，？代表匹配1個(gè)字符，&代表連接符。

公式：

=VLOOKUP("*"&F17&"*",$B$16:$D$21,3,0)

3、文本數(shù)值混合查找（連接符 &）

公式：

=VLOOKUP(F27&"",$B$26:$C$31,2,0)

當(dāng)查找對(duì)象和查找區(qū)域格式不一致，一個(gè)為數(shù)值格式，一個(gè)為文本格式，如果用基礎(chǔ)的vlookup函數(shù)查找會(huì)報(bào)錯(cuò)，我們可以用查找對(duì)象+連接符&+""來(lái)和vlookup嵌套使用來(lái)查找，如下圖：

4、去除空格查找（substitute）

我們有時(shí)候遇到的數(shù)據(jù)總是會(huì)有一些空格，如果需要匹配查找數(shù)據(jù)，處理起來(lái)會(huì)很麻煩。

substitute函數(shù)單獨(dú)使用是替換函數(shù)，如果和vlookup函數(shù)嵌套使用，就不需要先去除一遍空格然后再查找。

substitute函數(shù)語(yǔ)法：substitute（字符串，原字符串，新字符串）

語(yǔ)法說(shuō)明：

【字符串】需要替換的單元格。

【原字符串】需要替換單元格內(nèi)的什么內(nèi)容。

【新字符串】想要替換的新內(nèi)容。

示例：將F38單元格內(nèi)的空格替換掉，如下圖：

公式：

=SUBSTITUTE(F38," ","")

嵌套使用查找示例：將姓名去除掉空格后查找所對(duì)應(yīng)的工資是多少？

場(chǎng)景1：

公式：

=VLOOKUP(SUBSTITUTE(F38," ",""),$B$37:$D$42,3,0)

場(chǎng)景2：

公式：

{=VLOOKUP(F46,SUBSTITUTE($B$45:$D$50," ",""),3,0)}

需要注意的是，如果這樣嵌套的話，需要用大括號(hào){}給公式包括，然后再按住ctrl+shift+Enter，否則會(huì)出錯(cuò)#VALUE!。

5、去除不可見字符查找（clean）

有時(shí)候有些數(shù)據(jù)看起來(lái)沒(méi)有什么區(qū)別，但是它是有些空白的空格，如果直接查找會(huì)報(bào)錯(cuò)#N/A，如下圖：

這時(shí)候可以用clean函數(shù)嵌套vlookup函數(shù)使用。

公式：{=VLOOKUP(CLEAN(F56),CLEAN($B$55:$D$60),3,FALSE)}

需要注意的是我用的WPS也是需要加大括號(hào)按ctrl+shift+Enter，否則也會(huì)出錯(cuò)。

6、多列批量查找（column）

column是引用函數(shù)，用于返回指定引用的列號(hào)。

語(yǔ)法：column（需要返回的列的序號(hào)）

用法就是你想知道某個(gè)單元格在第幾列，就可以用這個(gè)函數(shù)返回列數(shù)，如下圖：

當(dāng)我們的查找對(duì)象和查找區(qū)域格式列名順序都一樣時(shí)，可以用column函數(shù)嵌套vlookup函數(shù)進(jìn)行多列批量查找，直接拖動(dòng)就全部查找，如下圖：

公式：

=VLOOKUP($G76,$B$75:$E$80,COLUMN(B1),0)

需要注意的是$G76查找對(duì)象需要引用，行變列不變，否則拖動(dòng)會(huì)出錯(cuò)。

7、多列動(dòng)態(tài)查找（match）

match函數(shù)的用法和vlookup函數(shù)差不多，都是匹配函數(shù)，match函數(shù)是相互匹配，可以查找某個(gè)值在查找區(qū)域的那個(gè)位置，例如查找A列的姓名在B列的那個(gè)位置。

match函數(shù)和vlookup函數(shù)嵌套，可以多列動(dòng)態(tài)查找，不像和colunm嵌套一樣需要查找對(duì)象列名順序都要和查找區(qū)域一致才行。

match函數(shù)語(yǔ)法：match（查找值，查找區(qū)域，匹配類型）

語(yǔ)法說(shuō)明：

【查找值】查找的對(duì)象。

【查找區(qū)域】在那個(gè)區(qū)域查找，查找范圍。

【匹配類型】0代表精準(zhǔn)查找，1代表模糊查找。

match函數(shù)和vlookup函數(shù)嵌套使用，可以多列動(dòng)態(tài)查找，如圖示列：

公式：

=VLOOKUP($G106,$B$105:$E$110,MATCH(H$105,$B$105:$E$105),0)

原理：match函數(shù)會(huì)返回查找對(duì)象的列在查找區(qū)域的位置數(shù)，然后vlookup在進(jìn)行查找匹配。上述公式相當(dāng)于就是

=VLOOKUP($G106,$B$105:$E$110,3,0)

8、一對(duì)多查找（countif）

案列：查找同一個(gè)部門不同的員工是哪些？

第一步：添加輔助列，用連接符&和countif函數(shù)給部門加上序號(hào)，例如市場(chǎng)部有三個(gè)分別標(biāo)上序號(hào)，如下圖：

公式：

=C85&COUNTIF($C$85:C85,C85)

第二步：再按照輔助列來(lái)匹配市場(chǎng)部的員工，匹配不到的員工就留空白單元格。

公式：

=IFERROR(VLOOKUP($G85&COLUMN(A1),$B$84:$D$92,3,0),"")

公式解釋：這里嵌套column引用函數(shù)是為了給查找值加上序號(hào)數(shù)，去匹配輔助列；iferror函數(shù)是將部門沒(méi)有員工的保留空白單元格顯示。

9、多行合并查找

案列：查找同一個(gè)部門不同的員工是哪些？

第一步：添加輔助列，先將市場(chǎng)部的員工查找出來(lái)連接在一起，如下圖：

公式：

=C98&IFERROR("、"&VLOOKUP(B98,B99:$D$106,3,0),"")

第二步：再使用vlookup函數(shù)根據(jù)部門名稱，匹配輔助列，如下圖：

公式：

=VLOOKUP(F98,$B$97:$D$105,3,0)

10、多表混合查找（if）

if函數(shù)是條件判斷函數(shù)。

if函數(shù)語(yǔ)法：=if（條件，真值，假值）

解釋：當(dāng)滿足條件時(shí)，返回一個(gè)真值，否則就返回一個(gè)假值，這個(gè)函數(shù)和SQL語(yǔ)句用法一樣的。

案列：根據(jù)是否是會(huì)員及消費(fèi)金額來(lái)匹配對(duì)應(yīng)的贈(zèng)品是什么？

場(chǎng)景：會(huì)員和非會(huì)員是單獨(dú)存放的表格，需要查找在一個(gè)表格中，如下圖：

公式：

=IFERROR(VLOOKUP(G111,IF(F111="是",$B$111:$C$114,$B$117:$C$120),2,TRUE),"")

解釋：這個(gè)就是把vlookup函數(shù)的查找范圍先用條件函數(shù)判斷一下該去那個(gè)表范圍查找。如下圖:

11、跨多表查找（indirect）

indirect函數(shù)可以把一個(gè)文本地址轉(zhuǎn)換為真正的地址，即返回文本字符串所指定的內(nèi)引用。

indirect函數(shù)語(yǔ)法：=indirect（單元格引用，引用的樣式）

【單元格引用】對(duì)指定單元格中數(shù)據(jù)內(nèi)容的引用。

【引用的樣式】指定單元格的引用類型，可不寫。

案例：查找相應(yīng)產(chǎn)品1月到3月的銷售金額，每個(gè)月的銷售金額單獨(dú)存放在不同的工作表。

可以使用column函數(shù)得到表1，表2，表3的地址，再用indirect把文本地址轉(zhuǎn)換，不然會(huì)出錯(cuò)，具體用法如下圖：

公式：

=VLOOKUP($B126,INDIRECT("表"&COLUMN(A1)&"!$A$3:$F$8"),6,0)

高階用法（數(shù)組）

1、反向查找

上面我們反向查找是通過(guò)構(gòu)建輔助列或者是手動(dòng)交換兩列位置來(lái)進(jìn)行查找，其實(shí)可以數(shù)組自動(dòng)交換位置實(shí)現(xiàn)反向查找，如下圖：

公式：

=VLOOKUP(G7,IF({1,0},$C$7:$C$11,$B$7:$B$11),2,0)

解釋：IF({1,0}表示無(wú)論值是真還是假都返回值

2、多條件查找

案例：查找相同水果到不同產(chǎn)地的市場(chǎng)價(jià)。

公式：

=VLOOKUP(G17&H17,IF({1,0},$B$17:$B$21&$C$17:$C$21,$E$17:$E$21),2,0)

其中IF({1,0},$B$17:$B$21&$C$17:$C$21,$E$17:$E$21)就是將水果和產(chǎn)地連接一起成一列和市場(chǎng)價(jià)返回成新的數(shù)組，當(dāng)做vlookup函數(shù)的查找區(qū)域，如下圖：

3、一對(duì)多查找

案列：查找不同區(qū)域的員工有哪些。

公式：

=IFERROR(VLOOKUP(COLUMN(A1),IF({1,0},COUNTIF(INDIRECT("b27:b"&ROW($27:$34)),$G27),$C$27:$C$34),2,0),"")

這個(gè)就是前面的函數(shù)多層嵌套，可以實(shí)現(xiàn)自動(dòng)查找。

好了這期內(nèi)容就分享完了，相信日常工作中都能用到，其中一些較難的也有其它方式可以實(shí)現(xiàn)，可以關(guān)注我，了解更多數(shù)據(jù)處理的方法。