函數(shù)篇：人見人愛的VLOOKUP，你真的會用他嗎？

L羅樂 2017-07-12

展開全文

【2】工作實際運用Excel案例，帶你從入門走向技巧帝；
【3】開設(shè)excel培訓(xùn)課程、工作運用工具開發(fā)。

Hello，大家好，首先允許我做下自我介紹，我就是傳說中的人見人愛花見花開車見車爆胎的號稱最常用的、使用頻率最高的、人送外號大眾情人的VLOOKUP是也！

關(guān)于我的秘密，你真正了解多少呢？下面我將從頭到腳的重新介紹下我自己，看能俘獲多少少女的心！

函數(shù)語法解析

1、函數(shù)定義：

在數(shù)據(jù)表的首列查找指定的值，并返回數(shù)據(jù)表當前行中指定列處的值。

2、語法格式：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（查找值，查找區(qū)域，要返回的結(jié)果在查找區(qū)域的第幾列，匹配方式）

3、參數(shù)說明：

①、lookup_value（必需）：要查找的值，可以為數(shù)值、引用或文本字符串。查找文本時，文本不區(qū)分大小寫。

②、Table_array（必需）：查找區(qū)域，可以使用對區(qū)域或區(qū)域名稱的引用、常數(shù)數(shù)組、計算后的內(nèi)存數(shù)組。并要求查找值在該區(qū)域的第一列，且其它列包含需返回的內(nèi)容。

③、col_index_num（必需）：要返回的結(jié)果在查找區(qū)域中的序列號，可以為數(shù)字或結(jié)果為數(shù)字的表達式。

如果col_index_num小于1，函數(shù)VLOOKUP返回錯誤值值#VALUE!；

如果col_index_num大于table_array的列數(shù)，函數(shù)VLOOKUP返回錯誤值#REF!。

④、range_lookup（可選）：查找方式，指明是近似匹配和精確匹配。

如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；

Table_array中的首列值必須以升序排序；

如果為FALSE或0，函數(shù)VLOOKUP將返回精確匹配值，如果找不到，則返回錯誤值#N/A。

4、注意事項：

①、在查找區(qū)域的第一列中搜索文本值時，請確保其第一列中的數(shù)據(jù)沒有前導(dǎo)空格、尾部空格、直引號(' 或 )與彎引號(‘ 或 “) 不一致或非打印字符。否則，VLOOKUP可能返回不正確或意外的值。

②、在搜索數(shù)字或日期值時，請確保查找區(qū)域第一列中的數(shù)據(jù)未存儲為文本值。否則，VLOOKUP可能返回不正確或意外的值。

③、如果range_lookup是精確匹配且lookup_value為文本，則可以在lookup_value中使用通配符問號 (?) 和星號 (*)。

問號匹配任意單個字符；星號匹配任意一串字符。如果要查找通配符本身，則該字符前鍵入波形符（~）。

函數(shù)示例

▲

精確匹配

公式：

=VLOOKUP(F3,A3:D8,4,0)

解析：

第一參數(shù)：查找值，查找君柳，輸入F3

第二參數(shù)：查找區(qū)域，注意查找區(qū)域的首列要包含查找值，區(qū)域為A3:D8

第三參數(shù)：要返回的結(jié)果在查找區(qū)域的第幾列，愛好在查找區(qū)域的第4列，所以為4

第四參數(shù)：匹配方式，精確匹配，輸入FALSE或0

▲

近似匹配

公式：

=VLOOKUP(B14,E$14:F$17,2)

解析：

第一參數(shù)：查找值，查找業(yè)績，輸入B14

第二參數(shù)：查找區(qū)域，查找區(qū)域的首列要包含查找值,區(qū)域為E14:F17

第三參數(shù)：要返回的結(jié)果在查找區(qū)域的第幾列，等級在查找區(qū)域的第2列，所以為2

第四參數(shù)：匹配方式，近似匹配，為TRUE或省略

以查找B14單元格9847為例，近似匹配，則返回小于9847的最大數(shù)值，即9000，其對應(yīng)的等級為優(yōu)。

注意：查找區(qū)域中的首列值必須以升序排序

▲

查找第一次采購單價

公式：

=VLOOKUP(E25,B25:C36,2,)

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

▲

通配符查找

公式：

=IFNA(VLOOKUP('*'&D42&'*',A$42:B$46,2,),'')

解析：全稱中包含簡稱，用通配符星號（*），星號（*）匹配任意一串字符。

查找不到時會返回錯誤值#N/A，可以用函數(shù)IFERROR或IFNA容錯。

有關(guān)函數(shù)IFERROR和IFNA的用法，詳情請點擊下面鏈接：

函數(shù)篇：容錯高手IFERROR與IFNA

▲

帶“~”的查找

公式：

=VLOOKUP(F53,A53:B58,2,0)

查找值F53，查找區(qū)域A53:B58，地區(qū)在查找區(qū)域的第2列，精確查找，沒錯啊，為什么結(jié)果會顯示錯誤值呢？

原因在于波形符（~）

公式：

=VLOOKUP(SUBSTITUTE(F53,'~','~~'),A53:B58,2,0)

解析：波形符（~）作為通配符，在查找包含其本身的值時，需在~前鍵入~，本題中用函數(shù)SUBSTITUTE將~替換成~~。

▲

格式不一致的查找

分兩種情況：

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

出現(xiàn)錯誤值的原因在于格式不統(tǒng)一

正確解法：

公式：

=VLOOKUP(D64*1,A64:B69,2,0)

解析：將查找值轉(zhuǎn)換為和查找區(qū)域首列的值一樣的格式

轉(zhuǎn)換的方式很多種，比如： 0，-0，--，*1，/1，^1......等等。

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

同樣的，出現(xiàn)錯誤值的原因在于格式不統(tǒng)一

正確解法：

公式：

=VLOOKUP(D75&'',A75:B80,2,0)

解析：查找值數(shù)值型，查找區(qū)域文本型，將查找值連接個空（&''）變?yōu)槲谋荆袷浇y(tǒng)一后就能查找出正確結(jié)果了。

▲

取消合并單元格

第一種：全部為文本

公式：=VLOOKUP('座',A$86:A86,1,1)

或者：=VLOOKUP('々',A$86:A86,1,1)

注意：匹配方式為近似匹配

“々”很多人都打不出來，可以按快捷鍵<Alt 41385>

第二種：全部為數(shù)值

公式：

=VLOOKUP(9E 307,A$108:A108,1,1)

注意：匹配方式為近似匹配

這兩種情況不要說只有函數(shù)LOOKUP能做到，VLOOKUP照樣做得到。

▲

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

公式：

=VLOOKUP($F130,$A130:$D135,COLUMN(B1),0)，向右填充

解析：地區(qū)、性別、愛好分別在查找區(qū)域的第2、3、4列，可以用函數(shù)COLUMN構(gòu)造；

COLUMN：返回一引用的列號

COLUMN(B1)即=2，公式右拉變成COLUMN(C1)、COLUMN(D1)正好得到列號2、3、4，不需要手動更改數(shù)字。

▲

反向查找

公式：

=VLOOKUP($F141,IF({1,0},$D141:$D146,A141:A146),2,0)，向右填充

解析：

IF（條件，條件成立時返回的值，條件不成立時返回的值）

有關(guān)函數(shù)IF的用法，詳情請點擊下面鏈接：

函數(shù)篇：小小IF不簡單

以IF({1,0},$D141:$D146,A141:A146)為例解釋下IF{1,0}結(jié)構(gòu):

{1,0}是函數(shù)IF中的條件

當為1時條件成立返回$D141:$D146

當為0時條件不成立返回A141:A146

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

公式也可以寫成：

=VLOOKUP($F141,IF({0,1},A141:A146,$D141:$D146),2,0)

VLOOKUP IF{0,1}可以實現(xiàn)逆向查找，VLOOKUP CHOOSE組合照樣可以

公式：

=VLOOKUP($F141,CHOOSE({1,2},$D141:$D146,A141:A146),2,0)

▲

交叉查詢

公式：

=VLOOKUP(F152,A152:D157,MATCH(G152,A151:D151,0),0)

解析：用函數(shù)MATCH找到要返回的值在查找區(qū)域的第幾列

MATCH:返回符合特定值特定順序的項在數(shù)組中的相應(yīng)位置。

MATCH（查找值，查找區(qū)域，查找方式）

MATCH(G152,A151:D151,0)即在區(qū)域A151:D151中精確查找5月的位置為3

所以用函數(shù)VLOOKUP查找業(yè)務(wù)員為君柳時其返回區(qū)域A152:D157中對應(yīng)的第3列中的值即6062。

▲

合并單元格的引用問題

公式：

=VLOOKUP('座',OFFSET(A163,,,MATCH(G163,B163:B179,)),1,1)

解析：MATCH(G163,B163:B179,)部分找到客服“君柳”在區(qū)域B163:B179中的位置為8

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

OFFSET(A163,,,MATCH(G163,B163:B179,))是以A163單元格為基點，偏移0行0列，返回行高為8的新區(qū)域A163:A170的引用。

抹黑按F9得到：

用“座”或“々”等較大的漢字查找區(qū)域中最后一個單元格內(nèi)容。

▲

合并單元格的查詢問題

公式：

=VLOOKUP(VLOOKUP('々',A$185:A185,1,1),G:H,2,0)

解析：用VLOOKUP('々',A$185:A185,1,1)查找出對應(yīng)區(qū)域中最后一個單元格內(nèi)容；

外層再套個VLOOKUP精確查找出對應(yīng)直播課程的單價。

▲

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

公式：

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