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

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

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

函數語法解析

1、函數定義：

在數據表的首列查找指定的值，並返回數據表當前行中指定列處的值。

2、語法格式：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（查找值，查找區域，要返回的結果在查找區域的第幾列，匹配方式）

3、參數說明：

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

②、Table_array（必需）：查找區域，可以使用對區域或區域名稱的引用、常數數組、計算後的內存數組。並要求查找值在該區域的第一列，且其它列包含需返回的內容。

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

如果col_index_num小於1，函數VLOOKUP返回錯誤值值#VALUE!；

如果col_index_num大於table_array的列數，函數VLOOKUP返回錯誤值#REF!。

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

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

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

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

4、注意事項：

①、在查找區域的第一列中搜索文本值時，請確保其第一列中的數據沒有前導空格、尾部空格、直引號(" 或 )與彎引號(『 或 「) 不一致或非列印字元。否則，VLOOKUP可能返回不正確或意外的值。

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

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

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

函數示例

▲

01

精確匹配

公式：

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

解析：

第一參數：查找值，查找君柳，輸入F3

第二參數：查找區域，注意查找區域的首列要包含查找值，區域為A3:D8

第三參數：要返回的結果在查找區域的第幾列，愛好在查找區域的第4列，所以為4

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

▲

02

近似匹配

公式：

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

解析：

第一參數：查找值，查找業績，輸入B14

第二參數：查找區域，查找區域的首列要包含查找值,區域為E14:F17

第三參數：要返回的結果在查找區域的第幾列，等級在查找區域的第2列，所以為2

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

以查找B14單元格9847為例，近似匹配，則返回小於9847的最大數值，即9000，其對應的等級為優。

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

▲

03

查找第一次採購單價

公式：

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

解析：當查找區域首列出現有兩個或更多值與查找值匹配時，函數VLOOKUP返回第一次出現的對應值。

▲

04

通配符查找

公式：

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

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

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

有關函數IFERROR和IFNA的用法，詳情請點擊下面鏈接：

函數篇：容錯高手IFERROR與IFNA

▲

05

帶「~」的查找

公式：

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

查找值F53，查找區域A53:B58，地區在查找區域的第2列，精確查找，沒錯啊，為什麼結果會顯示錯誤值呢？

原因在於波形符（~）

公式：

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

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

▲

06

格式不一致的查找

分兩種情況：

第一種：查找值文本型，查找區域數值型

出現錯誤值的原因在於格式不統一

正確解法：

公式：

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

解析：將查找值轉換為和查找區域首列的值一樣的格式

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

第二種：查找值數值型，查找區域文本型

同樣的，出現錯誤值的原因在於格式不統一

正確解法：

公式：

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

解析：查找值數值型，查找區域文本型，將查找值連接個空（&""）變為文本，格式統一後就能查找出正確結果了。

▲

07

取消合併單元格

第一種：全部為文本

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

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

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

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

第二種：全部為數值

公式：

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

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

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

▲

08

查找返回多列數據

公式：

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

解析：地區、性別、愛好分別在查找區域的第2、3、4列，可以用函數COLUMN構造；

COLUMN：返回一引用的列號

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

▲

09

反向查找

公式：

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

解析：

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

有關函數IF的用法，詳情請點擊下面鏈接：

函數篇：小小IF不簡單

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

{1,0}是函數IF中的條件

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

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

整體來說就是兩列順序對換，將逆序轉換為順序。

公式也可以寫成：

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

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

公式：

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

▲

10

交叉查詢

公式：

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

解析：用函數MATCH找到要返回的值在查找區域的第幾列

MATCH:返回符合特定值特定順序的項在數組中的相應位置。

MATCH（查找值，查找區域，查找方式）

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

所以用函數VLOOKUP查找業務員為君柳時其返回區域A152:D157中對應的第3列中的值即6062。

▲

11

合併單元格的引用問題

公式：

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

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

OFFSET（基點，偏移行數，偏移列數，行高，列寬）

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

抹黑按F9得到：

用「座」或「々」等較大的漢字查找區域中最後一個單元格內容。

▲

12

合併單元格的查詢問題

公式：

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

解析：用VLOOKUP("々",A$185:A185,1,1)查找出對應區域中最後一個單元格內容；

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

▲

13

按指定次數重複

公式：

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

數組公式，按<Ctrl Shift Enter>三鍵結束。

▲

14

與T IF的組合應用

公式：

=SUM(VLOOKUP(T(IF({1},A221:A228)),D221:E228,2,0)*B221:B228)

數組公式，按<Ctrl Shift Enter>三鍵結束。

T起降維作用

▲

15

多條件查找

公式：

=VLOOKUP(E236&F236,IF({1,0},A$236:A$243&B$236:B$243,C$236:C$243),2,0)

數組公式，按<Ctrl Shift Enter>三鍵結束。

VLOOKUP CHOOSE組合實現多條件查找：

公式：

=VLOOKUP(E236&F236,CHOOSE({1,2},A$236:A$243&B$236:B$243,C$236:C$243),2,0)

數組公式，按<Ctrl Shift Enter>三鍵結束。

▲

16

一對多查找

公式：

=IFERROR(VLOOKUP(D$249&ROW(A1),IF({1,0},A$249:A$257&COUNTIF(INDIRECT("A262:A"&ROW($249:$257)),D$249),B$249:B$257),2,),"")

數組公式，按<Ctrl Shift Enter>三鍵結束。

同樣的IF{1,0}改成CHOOSE{1,2}也可以。

光說不練假把式，動手操作才是硬道理！

作者：仰望~星空