說起Excel中的數(shù)據(jù)查找，VLOOKUP可真是大名鼎鼎。屬于Excel“查找王者”！VLOOKUP函數(shù)果真所向披靡嗎？今天就和大家一起說說Excel中的數(shù)據(jù)查詢那些事兒。深入了解一下VLOOKUP函數(shù)的各種用法，看看這位大眾情人還藏著多少不為人知的秘密！

功能：在表格的首列查找指定的數(shù)值，并返回表格當(dāng)前行中指定列處的數(shù)值。

結(jié)構(gòu)：=VLOOKUP(查找值,查找區(qū)域,列序數(shù),匹配條件)

解釋：=VLOOKUP(找誰,在哪里找,第幾列,0或1)

說明：

1、第一參數(shù)：查找值（找誰），比如說根據(jù)【姓名】來查找【成績】，【姓名】就是查找值

2、第二參數(shù)：查找區(qū)域（在哪里找），查找的數(shù)據(jù)區(qū)域也就是所選擇的區(qū)域，注意所選擇的區(qū)域要根據(jù)查找值位于第一列開始選擇，比如說根據(jù)【姓名】來查找【成績】，那數(shù)據(jù)表所選的區(qū)域要從【姓名】列開始選擇。建議設(shè)置為絕對引用，在選定區(qū)域后按F4鍵就可以快速切換，就是在行和列的前面添加$符號，拖動公式時，區(qū)域就不會發(fā)生改變；

3、第三參數(shù)：列序數(shù)（第幾列），也就是返回的結(jié)果在數(shù)據(jù)表中位于第幾列，包含隱藏的列；

4、第四參數(shù)：匹配條件（0或1），若為0或FALSE代表精確匹配，1或TRUE代表近似匹配；

注：查找值在數(shù)據(jù)表中多次出現(xiàn)，導(dǎo)致有多個結(jié)果，函數(shù)僅僅會返回第一個找到的結(jié)果。

關(guān)注“Excel辦公小課堂”公眾號

回復(fù)VLOOKUP即可領(lǐng)取練習(xí)素材

第1種用法：精確匹配（0）

目的：根據(jù)【銷售員】查找【銷售額】

公式：=VLOOKUP(F2,$B$1:$D$6,3,0)

說明：“查找值”F2也就是【銷售員】，“查找區(qū)域”根據(jù)查找值【銷售員】在所選的數(shù)據(jù)表中，也就是$B$1:$D$6中必須位于第一列?！傲行驍?shù)”為3，表示對應(yīng)結(jié)果【銷售額】在所選區(qū)域中排第3列；“匹配條件”為0，表示精確匹配，若為1則代表近似匹配。

第2種用法：近似匹配（1）

目的：根據(jù)【銷售額】查找【提成比】計算【業(yè)績提成】

公式：=VLOOKUP(F2,$B$1:$C$6,2,1)*F2

說明：“查找值”F2也就是【銷售額】，“查找區(qū)域”根據(jù)查找值【銷售額】在所選的數(shù)據(jù)表中，也就是$B$1:$C$6中必須位于第一列?！傲行驍?shù)”為2，表示對應(yīng)結(jié)果【提成比】在所選區(qū)域中排第2列；“匹配條件”為1，表示近似匹配

第3種用法：反向查找（輔助列）

目的：根據(jù)【銷售員】查找【工號】

公式：=VLOOKUP(G2,$B$1:$E$6,4,0)

說明：“查找值”G2也就是【銷售員】，由于數(shù)據(jù)表中【銷售員】列在【工號】列的后面，無法按照VLOOKUP函數(shù)的使用方法（查找區(qū)域要根據(jù)查找值位于第一列進(jìn)行）所以我們要將【銷售員】排列在數(shù)據(jù)表中的第一列，則增加一列【輔助列】代表【工號】列，數(shù)據(jù)表所選區(qū)域也就是$B$1:$E$6區(qū)域。“列序數(shù)”為4，表示對應(yīng)結(jié)果【輔助列】在所選區(qū)域中排第4列；“匹配條件”為0，表示精確匹配

或者在數(shù)據(jù)表前面增加銷售員【輔助列】

第4種用法：多條件查找（輔助列）

目的：根據(jù)【水果】及【產(chǎn)地】查找【市場價】

公式：=VLOOKUP(G2&H2,$A$1:$E$6,5,0)

說明：由于查找值【水果】及【產(chǎn)地】在不同列中，這里需要用連接字符&將兩列值相連接。則“查找值”G2&H2也就是【水果】及【產(chǎn)地】，數(shù)據(jù)表中【水果】及【產(chǎn)地】也在不同列，無法按照VLOOKUP函數(shù)的使用方法（查找區(qū)域要根據(jù)查找值位于第一列進(jìn)行）所以我們要將【水果】及【產(chǎn)地】錄入在同一列中，則增加一列【輔助列】代表【水果產(chǎn)地】列，數(shù)據(jù)表所選區(qū)域也就是$A$1:$E$6區(qū)域?！傲行驍?shù)”為5，表示對應(yīng)結(jié)果【市場價】在所選區(qū)域中排第5列；“匹配條件”為0，表示精確匹配 

第5種用法：屏蔽錯誤值（IFERROR）

目的：根據(jù)【產(chǎn)品型號】查找【產(chǎn)品等級】

公式：=IFERROR(VLOOKUP(E2,$A$1:$C$6,3,0),"")

說明：“查找值”E2也就是【產(chǎn)品型號】，“查找區(qū)域”根據(jù)查找值【產(chǎn)品型號】在所選的數(shù)據(jù)表中，也就是$A$1:$C$6中必須位于第一列。“列序數(shù)”為3，表示對應(yīng)結(jié)果【產(chǎn)品等級】位于所選區(qū)域中排第3列，“匹配條件”為0，表示精確匹配

由于部分查找值【產(chǎn)品型號】在數(shù)據(jù)表中不存在，則會出現(xiàn)#N/A錯誤值，為了屏蔽錯誤值需嵌套IFERROR函數(shù)

IFERROR函數(shù):表示的是公式中計算結(jié)果錯誤，則會返回指定的值，否則返回公式的結(jié)果

=IFERROR（公式計算結(jié)果的值，屏蔽錯誤的值）

IFERROR公式中VLOOKUP(E2,$A$1:$C$6,3,0)表示的公式計算結(jié)果的值

IFERROR公式中""表示計算結(jié)果為#N/A錯誤值時，讓其最終結(jié)果顯示為空，則輸入英文雙引號表示屏蔽#N/A錯誤值

第6種用法：關(guān)鍵字查找（通配符*）

目的：根據(jù)【公司簡稱】查找【公司人數(shù)】

公式：=VLOOKUP("*"&D2&"*",$A$1:$B$6,2,0)

說明：“查找值”"*"&D2&"*"也就是通過【公司簡稱】查找【公司全稱】，這里的星號“*”表示的是任意字符，例如【公司簡稱】為“利美德”通過前后連接上星號“*”就表示的是“利美德”前后加上任意字符，在此數(shù)據(jù)表中所表示的是“浙江利美德教具有限公司”。

“查找區(qū)域”根據(jù)查找值【公司簡稱】在所選的數(shù)據(jù)表中，也就是$A$1:$B$6中必須位于第一列。“列序數(shù)”為2，表示對應(yīng)結(jié)果【公司人數(shù)】位于所選區(qū)域中排第2列，“匹配條件”為0，表示精確匹配

第7種用法：文本數(shù)值混合查找（連接符&）

目的：根據(jù)【工號】查找【銷售員】

公式：=VLOOKUP(E2&"",$A$1:$B$6,2,0)

說明：“查找值”E2&""也就是通過【工號】查找【銷售員】，由于查找值【工號】是一個數(shù)值，而查找區(qū)域中【工號】是文本字符，所以在這里通過連接字符&””連接一個空文本英文的雙引號，表示的是將數(shù)值轉(zhuǎn)換成文本。

“查找區(qū)域”根據(jù)查找值【工號】在所選的數(shù)據(jù)表中，也就是$A$1:$B$6中必須位于第一列?！傲行驍?shù)”為2，表示對應(yīng)結(jié)果【銷售員】位于所選區(qū)域中排第2列，“匹配條件”為0，表示精確匹配。

若通過文本型的【工號】查找【銷售額】，公式則為=VLOOKUP(A2*1,$E$1:$G$6,3,0)

說明：查找值A(chǔ)2*1表示的是將文本型【工號】轉(zhuǎn)換成數(shù)值型【工號】，理解為任意一個文本型數(shù)字通過運算就會變成常規(guī)數(shù)值。

“查找區(qū)域”根據(jù)查找值【工號】在所選的數(shù)據(jù)表中，也就是$E$1:$G$6中必須位于第一列?！傲行驍?shù)”為3，表示對應(yīng)結(jié)果【銷售額】位于所選區(qū)域中排第3列，“匹配條件”為0，表示精確匹配

第8種用法：去除空格查找（SUBSTITUTE）

目的：根據(jù)【銷售員】查找【銷售額】

公式：=VLOOKUP(G2,SUBSTITUTE($B$1:$D$6," ",""),3,0)

說明：“查找值”G2也就是通過【銷售員】查找【銷售額】，由于查找區(qū)域中【銷售員】列存在空格，通過VLOOKUP函數(shù)常規(guī)計算會出現(xiàn)#N/A錯誤值

在這里需要將查找區(qū)域中空格通過SUBSTITUTE函數(shù)進(jìn)行去除

SUBSTITUTE函數(shù)：表示的是將字符串中的部分字符替換成新字符串

=SUBSTITUTE（字符串，原字符串，新字符串）

公式中SUBSTITUTE($B$1:$D$6," ","")表示的是將空格進(jìn)行去除

“查找區(qū)域”根據(jù)查找值【銷售員】在所選的數(shù)據(jù)表中，也就是SUBSTITUTE($B$1:$D$6," ","")中必須位于第一列?！傲行驍?shù)”為3，表示對應(yīng)結(jié)果【銷售額】位于所選區(qū)域中排第3列，“匹配條件”為0，表示精確匹配。

最后注意由于當(dāng)前公式=VLOOKUP(G2,SUBSTITUTE($B$1:$D$6," ",""),3,0)是數(shù)組公式需要三鍵結(jié)束，按Ctrl+Shift+Enter得到對應(yīng)結(jié)果若查找值中包含空格，則公式為=VLOOKUP(SUBSTITUTE(G9," ",""),$B$8:$D$13,3,0)

第9種用法：去除不可見字符查找（CLEAN）

目的：根據(jù)【銷售員】查找【銷售額】

公式：=VLOOKUP(F2,CLEAN($B$1:$D$6),3,0)

說明：“查找值”F2也就是通過【銷售員】查找【銷售額】，由于查找區(qū)域中【銷售員】列存在不可見字符，通過VLOOKUP函數(shù)常規(guī)計算會出現(xiàn)#N/A錯誤值

在這里需要將查找區(qū)域中不可見字符通過CLEAN函數(shù)進(jìn)行去除

CLEAN函數(shù)：表示的是刪除文本中不可見字符（非打印字符）

=CLEAN（字符串）

公式中CLEAN($B$1:$D$6)表示的是去除查找區(qū)域中的不可見字符

“查找區(qū)域”根據(jù)查找值【銷售員】在所選的數(shù)據(jù)表，也就是CLEAN($B$1:$D$6)中必須位于第一列?！傲行驍?shù)”為3，表示對應(yīng)結(jié)果【銷售額】位于所選區(qū)域中排第3列，“匹配條件”為0，表示精確匹配。

最后注意由于當(dāng)前公式=VLOOKUP(F2,CLEAN($B$1:$D$6),3,0)是數(shù)組公式需要三鍵結(jié)束，按Ctrl+Shift+Enter得到對應(yīng)結(jié)果

第10種用法：多列批量查找（COLUMN）

目的：根據(jù)【工號】查找【銷售員】、【地區(qū)】及【銷售額】

公式：=VLOOKUP($F2,$A$1:$D$6,COLUMN(B1),0)

說明：“查找值”$F2也就是通過【工號】查找【銷售員】、【地區(qū)】及【銷售額】，由于公式需要向右及向下填充，查找值列保持不變，需要將F列進(jìn)行鎖定也就是$F2

“查找區(qū)域”根據(jù)查找值【工號】在所選的數(shù)據(jù)表中，也就是$A$1:$D$6中必須位于第一列。

此案例中是多列批量查找“列序數(shù)”也就是所對應(yīng)的結(jié)果值在不同的列中，需要嵌套一個COLUMN函數(shù)。

COLUMN函數(shù)：表示返回引用的列號

=COLUMN（單元格）

公式中COLUMN(B1)表示的是返回B列的列號，也就第2列

“匹配條件”為0，表示精確匹配

第11種用法：多列定位批量查找（MATCH）

目的：根據(jù)【工號】查找【地區(qū)】、【銷售員】及【銷售額】

公式：=VLOOKUP($F2,$A$1:$D$6,MATCH(G$1,$A$1:$D$1,0),0)

說明：“查找值”$F2也就是通過【工號】查找【地區(qū)】、【銷售員】及【銷售額】，由于公式需要向右及向下填充，查找值列保持不變，需要將F列進(jìn)行鎖定也就是$F2

“查找區(qū)域”根據(jù)查找值【工號】在所選的數(shù)據(jù)表中，也就是$A$1:$D$6中必須位于第一列。

此案例中是多列批量查找“列序數(shù)”并且與需要查找數(shù)據(jù)的字段名稱與數(shù)據(jù)表中中的字段名稱位置不一致，需要嵌套一個MATCH函數(shù)（源數(shù)據(jù)字段名稱順序是【銷售員】【地區(qū)】【銷售額】需要查找數(shù)據(jù)表中字段名稱順序是【地區(qū)】【銷售員】【銷售額】）

MATCH函數(shù)：表示返回指定數(shù)值在指定數(shù)組區(qū)域中的位置

=MATCH（查找值,查找區(qū)域,匹配條件）

公式中MATCH(G$1,$A$1:$D$1,0)表示的是【地區(qū)】在$A$1:$D$1區(qū)域中的列號，也就第3列

“匹配條件”為0，表示精確匹配

第12種用法：一對多查找（COUNTIF）

目的：根據(jù)【地區(qū)】查找多名【銷售員】

公式：=IFERROR(VLOOKUP($G2&COLUMN(A1),$A$1:$E$6,4,0),"")

說明：此案例中各個地區(qū)有多名銷售員，而VLOOKUP函數(shù)只能查找最近的數(shù)據(jù)，無法查找多個數(shù)據(jù)，需要在數(shù)據(jù)表中中增加一列【輔助列】，公式為=C2&COUNTIF($C$2:C2,C2)

COUNTIF函數(shù)：表示計算區(qū)域中滿足給定條件的單元格的個數(shù)

=COUNTIF（區(qū)域，條件）

區(qū)域$C$2:C2：表示的是地區(qū)列，前面$C$2絕對引用，后面C2表示的是會隨著公式向下填充，單元格區(qū)域累積增加

條件C2：表示的是指定的條件計算單元格個數(shù)

如果單純的通過COUNTIF($C$2:C2,C2)計算得到的結(jié)果為1,1,2,1,2的數(shù)據(jù)，為了直觀體現(xiàn)出各個地區(qū)的個數(shù)，在COUNTIF連接一個C2單元格，得到結(jié)果為杭州1，寧波1，杭州2，溫州1，寧波1（這里的杭州2表示的是地區(qū)杭州有兩個）

VLOOKUP函數(shù)查找值$G2&COLUMN(A1)根據(jù)數(shù)據(jù)表中輔助列的形式地區(qū)名+數(shù)字，也就是通過【地區(qū)】查找多名【銷售員】（COLUMN函數(shù)請看VLOOKUP函數(shù)第10種用法說明）

“查找區(qū)域”根據(jù)查找值【輔助列】在所選的數(shù)據(jù)表中，也就是$A$1:$E$6中必須位于第一列，“列序數(shù)”為4，表示對應(yīng)結(jié)果【銷售員】位于所選區(qū)域中排第4列，“匹配條件”為0，表示精確匹配。

最后嵌套IFERROR（IFERROR函數(shù)請看VLOOKUP函數(shù)第5種用法說明）是因為溫州地區(qū)只有一名銷售員，通過公式填充會出現(xiàn)錯誤值，所以需要用IFERROR函數(shù)屏蔽錯誤值

第13種用法：多表混合查找（IF）

目的：根據(jù)【消費金額】查找【贈品】

公式：=IFERROR(VLOOKUP(H2,IF(G2="是",$A$2:$B$5,$C$2:$D$5),2,1),"無贈品")

說明：“查找值”H2也就是通過【【消費金額】查找【贈品】

由于“查找區(qū)域”是有多個區(qū)域，需要用IF函數(shù)來判斷是否是會員的情況，公式為IF(G2="是",$A$2:$B$5,$C$2:$D$5)

IF函數(shù)：表示的是根據(jù)指定的條件判斷，當(dāng)滿足指定的條件返回一個值，不滿足指定的條件返回相反的一個值

=IF（條件，滿足條件的值，不滿足條件的值）

此公式IF(G2="是",$A$2:$B$5,$C$2:$D$5)是根據(jù)條件是否是會員，當(dāng)是會員時返回對應(yīng)結(jié)果為【會員獎勵規(guī)則】區(qū)域，當(dāng)不是會員時返回對應(yīng)結(jié)果為【非會員獎勵規(guī)則】

“列序數(shù)”為2，表示對應(yīng)結(jié)果【贈品】位于所選區(qū)域中排第2列，“匹配條件”為1，表示近似匹配。

最后嵌套IFERROR（IFERROR函數(shù)請看VLOOKUP函數(shù)第5種用法說明）是因為低于2000消費金額時沒有贈品，通過公式填充會出現(xiàn)錯誤值，所以需要用IFERROR函數(shù)將錯誤值顯示為“無贈品”，當(dāng)然也可以直接輸入一個英文的雙引號，屏蔽錯誤值直接顯示為空。

第14種用法：跨多表查找（INDIRECT）

目的：根據(jù)【產(chǎn)品】查找每個月份的【銷售額】

公式：=VLOOKUP($B$1,INDIRECT(A2&"!A:B"),2,0)

說明：“查找值”$B$1也就是通過【產(chǎn)品】查找每個月份的【銷售額】，由于公式向下填充，查找值要絕對引用

此案例中“查找區(qū)域”在不同的工作表中，直接引用一個工作表中的數(shù)據(jù)區(qū)域，需要手動的更改工作表的名稱會比較麻煩，工作效率也會很低。

在此可以引用【月份】列的數(shù)值，但是直接引用月份列的數(shù)值，公式為A2&"!A:B"得到的結(jié)果是錯誤（原因是A2&"!A:B"是文本，無法參與計算得到錯誤值）

出現(xiàn)這種情況，需要用INDIRECT函數(shù)來解決，公式為INDIRECT(A2&"!A:B")

INDIRECT函數(shù)：表示的是返回由文本字符串指定的引用（簡單的理解為返回單元格的值）

=INDIRECT（單元格引用）

“列序數(shù)”為2，表示對應(yīng)結(jié)果【銷售額】位于所選區(qū)域中排第2列，“匹配條件”為0，表示精確匹配。

第15種用法：反向查找（數(shù)組或MATCH+INDEX）

目的：根據(jù)【銷售員】查找【工號】

公式：=VLOOKUP(F3,IF({1,0},$B$2:$B$6,$A$2:$A$6),2,0)

說明：“查找值”F3，也就是通過【銷售員】查找【工號】，“查找區(qū)域”由于在查找的數(shù)據(jù)表中【銷售員】列不在所選區(qū)域中排第一列（無法按照VLOOKUP函數(shù)的使用方法）。對于基礎(chǔ)用法中講解到增加輔助列可以查找對用的【工號】，現(xiàn)在無需增加輔助列結(jié)合IF函數(shù)數(shù)組用法可以實現(xiàn)反向查找，數(shù)組公式為IF({1,0},$B$2:$B$6,$A$2:$A$6)

第一參數(shù)：{1,0}，IF的第一參數(shù)是一個條件判斷的邏輯值，現(xiàn)在卻是{1,0}，在這里我們可以將1看作是True條件正確，將0看作是False條件錯誤，還需要注意的是1跟0是用大括號括起來的

當(dāng)IF函數(shù)條件為1時，返回$B$2:$B$6單元格區(qū)域，也就是【銷售員】區(qū)域

當(dāng)IF函數(shù)條件為0時，返回$A$2:$A$6單元格區(qū)域，也就是【工號】區(qū)域

選中數(shù)組公式IF({1,0},$B$2:$B$6,$A$2:$A$6)按F9直觀顯示對應(yīng)結(jié)果為{"洪寶坤","LM001";"凡克明","LM002";"曹錦榮","LM003";"周蒙","LM004";"鄭欣宜","LM005"}

最后VLOOKUP函數(shù)“列序數(shù)”為2，表示對應(yīng)結(jié)果【工號】位于所選區(qū)域中排第2列，就能得到對應(yīng)的【工號】

注：數(shù)組公式需要三鍵結(jié)束，按Ctrl+Shift+Enter得到對應(yīng)結(jié)果

若VLOOKUP函數(shù)結(jié)合IF函數(shù)數(shù)組公式難以理解，我們可以結(jié)合MATCH函數(shù)和INDEX函數(shù)查找對應(yīng)的【工號】。

公式為：=INDEX($A$2:$A$6,MATCH(F2,$B$2:$B$6,0))

MATCH函數(shù)：表示返回指定數(shù)值在指定數(shù)組區(qū)域中的位置

=MATCH（查找值,查找區(qū)域,匹配條件）

此案例中MATCH(F2,$B$2:$B$6,0)根據(jù)F2單元格也就是查找【銷售員】“曹錦榮”，在查找數(shù)據(jù)表中區(qū)域$B$2:$B$6，精確匹配，最后得到結(jié)果位置在第3行

INDEX函數(shù)：表示的是返回表格或區(qū)域中的值或值的引用

INDEX(區(qū)域，第幾行，第幾列)

此案例中INDEX($A$2:$A$6,MATCH(F2,$B$2:$B$6,0))，第一個參數(shù)根據(jù)指定【工號】區(qū)域$A$2:$A$6，第二個參數(shù)結(jié)合MATCH函數(shù)查找第3行對應(yīng)結(jié)果的【工號】為“LM003”，第三個參數(shù)可忽略

第16種用法：多條件查找（數(shù)組或MATCH+INDEX）

目的：根據(jù)【地區(qū)】和【銷售員】查找【銷售額】

公式：=VLOOKUP(F2&G2,IF({1,0},$B$2:$B$6&$C$2:$C$6,$D$2:$D$6),2,0)

說明：“查找值”F2&G2，也就是通過【地區(qū)】和【銷售員】查找【銷售額】，“查找區(qū)域”IF({1,0},$B$2:$B$6&$C$2:$C$6,$D$2:$D$6)（IF函數(shù)數(shù)組說明請看VLOOKUP函數(shù)第15種用法），“列序數(shù)”為2，表示對應(yīng)結(jié)果【銷售額】位于所選區(qū)域中排第2列，“匹配條件”為0，表示精確匹配。

注：數(shù)組公式需要三鍵結(jié)束，按Ctrl+Shift+Enter得到對應(yīng)結(jié)果

此案例也可以結(jié)合MATCH函數(shù)和INDEX函數(shù)查找對應(yīng)的【銷售額】

公式=INDEX($D$2:$D$6,MATCH(F2&G2,$B$2:$B$6&$C$2:$C$6,0))

MATCH函數(shù)和INDEX函數(shù)說明請看VLOOKUP函數(shù)第15種用法

注：數(shù)組公式需要三鍵結(jié)束，按Ctrl+Shift+Enter得到對應(yīng)結(jié)果