VLOOKUP函数16种经典用法

说起Excel中的数据查找，VLOOKUP可真是大名鼎鼎。属于Excel“查找王者”！VLOOKUP函数果真所向披靡吗？今天就和大家一起说说Excel中的数据查询那些事儿。深入了解一下VLOOKUP函数的各种用法，看看这位大众情人还藏着多少不为人知的秘密！

功能：在表格的首列查找指定的数值，并返回表格当前行中指定列处的数值。

结构：=VLOOKUP(查找值,查找区域,列序数,匹配条件)

解释：=VLOOKUP(找谁,在哪里找,第几列,0或1)

说明：

1、第一参数：查找值（找谁），比如说根据【姓名】来查找【成绩】，【姓名】就是查找值

2、第二参数：查找区域（在哪里找），查找的数据区域也就是所选择的区域，注意所选择的区域要根据查找值位于第一列开始选择，比如说根据【姓名】来查找【成绩】，那数据表所选的区域要从【姓名】列开始选择。建议设置为绝对引用，在选定区域后按F4键就可以快速切换，就是在行和列的前面添加$符号，拖动公式时，区域就不会发生改变；

3、第三参数：列序数（第几列），也就是返回的结果在数据表中位于第几列，包含隐藏的列；

4、第四参数：匹配条件（0或1），若为0或FALSE代表精确匹配，1或TRUE代表近似匹配；

注：查找值在数据表中多次出现，导致有多个结果，函数仅仅会返回第一个找到的结果。

第1种用法：精确匹配（0）

目的：根据【销售员】查找【销售额】

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

说明：“查找值”F2也就是【销售员】，“查找区域”根据查找值【销售员】在所选的数据表中，也就是$B$1:$D$6中必须位于第一列。“列序数”为3，表示对应结果【销售额】在所选区域中排第3列；“匹配条件”为0，表示精确匹配，若为1则代表近似匹配。

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

目的：根据【销售额】查找【提成比】计算【业绩提成】

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

说明：“查找值”F2也就是【销售额】，“查找区域”根据查找值【销售额】在所选的数据表中，也就是$B$1:$C$6中必须位于第一列。“列序数”为2，表示对应结果【提成比】在所选区域中排第2列；“匹配条件”为1，表示近似匹配

第3种用法：反向查找（辅助列）

目的：根据【销售员】查找【工号】

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

说明：“查找值”G2也就是【销售员】，由于数据表中【销售员】列在【工号】列的后面，无法按照VLOOKUP函数的使用方法（查找区域要根据查找值位于第一列进行）所以我们要将【销售员】排列在数据表中的第一列，则增加一列【辅助列】代表【工号】列，数据表所选区域也就是$B$1:$E$6区域。“列序数”为4，表示对应结果【辅助列】在所选区域中排第4列；“匹配条件”为0，表示精确匹配

或者在数据表前面增加销售员【辅助列】

第4种用法：多条件查找（辅助列）

目的：根据【水果】及【产地】查找【市场价】

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

说明：由于查找值【水果】及【产地】在不同列中，这里需要用连接字符&将两列值相连接。则“查找值”G2&H2也就是【水果】及【产地】，数据表中【水果】及【产地】也在不同列，无法按照VLOOKUP函数的使用方法（查找区域要根据查找值位于第一列进行）所以我们要将【水果】及【产地】录入在同一列中，则增加一列【辅助列】代表【水果产地】列，数据表所选区域也就是$A$1:$E$6区域。“列序数”为5，表示对应结果【市场价】在所选区域中排第5列；“匹配条件”为0，表示精确匹配

第5种用法：屏蔽错误值（IFERROR）

目的：根据【产品型号】查找【产品等级】

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

说明：“查找值”E2也就是【产品型号】，“查找区域”根据查找值【产品型号】在所选的数据表中，也就是$A$1:$C$6中必须位于第一列。“列序数”为3，表示对应结果【产品等级】位于所选区域中排第3列，“匹配条件”为0，表示精确匹配

由于部分查找值【产品型号】在数据表中不存在，则会出现#N/A错误值，为了屏蔽错误值需嵌套IFERROR函数

IFERROR函数:表示的是公式中计算结果错误，则会返回指定的值，否则返回公式的结果

=IFERROR（公式计算结果的值，屏蔽错误的值）

IFERROR公式中VLOOKUP(E2,$A$1:$C$6,3,0)表示的公式计算结果的值

IFERROR公式中""表示计算结果为#N/A错误值时，让其最终结果显示为空，则输入英文双引号表示屏蔽#N/A错误值

第6种用法：关键字查找（通配符*）

目的：根据【公司简称】查找【公司人数】

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

说明：“查找值”"*"&D2&"*"也就是通过【公司简称】查找【公司全称】，这里的星号“*”表示的是任意字符，例如【公司简称】为“利美德”通过前后连接上星号“*”就表示的是“利美德”前后加上任意字符，在此数据表中所表示的是“浙江利美德教具有限公司”。

“查找区域”根据查找值【公司简称】在所选的数据表中，也就是$A$1:$B$6中必须位于第一列。“列序数”为2，表示对应结果【公司人数】位于所选区域中排第2列，“匹配条件”为0，表示精确匹配

第7种用法：文本数值混合查找（连接符&）

目的：根据【工号】查找【销售员】

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

说明：“查找值”E2&""也就是通过【工号】查找【销售员】，由于查找值【工号】是一个数值，而查找区域中【工号】是文本字符，所以在这里通过连接字符&””连接一个空文本英文的双引号，表示的是将数值转换成文本。

“查找区域”根据查找值【工号】在所选的数据表中，也就是$A$1:$B$6中必须位于第一列。“列序数”为2，表示对应结果【销售员】位于所选区域中排第2列，“匹配条件”为0，表示精确匹配。

若通过文本型的【工号】查找【销售额】，公式则为=VLOOKUP(A2*1,$E$1:$G$6,3,0)

说明：查找值A2*1表示的是将文本型【工号】转换成数值型【工号】，理解为任意一个文本型数字通过运算就会变成常规数值。

“查找区域”根据查找值【工号】在所选的数据表中，也就是$E$1:$G$6中必须位于第一列。“列序数”为3，表示对应结果【销售额】位于所选区域中排第3列，“匹配条件”为0，表示精确匹配

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

目的：根据【销售员】查找【销售额】

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

说明：“查找值”G2也就是通过【销售员】查找【销售额】，由于查找区域中【销售员】列存在空格，通过VLOOKUP函数常规计算会出现#N/A错误值

在这里需要将查找区域中空格通过SUBSTITUTE函数进行去除

SUBSTITUTE函数：表示的是将字符串中的部分字符替换成新字符串

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

公式中SUBSTITUTE($B$1:$D$6," ","")表示的是将空格进行去除

“查找区域”根据查找值【销售员】在所选的数据表中，也就是SUBSTITUTE($B$1:$D$6," ","")中必须位于第一列。“列序数”为3，表示对应结果【销售额】位于所选区域中排第3列，“匹配条件”为0，表示精确匹配。

最后注意由于当前公式=VLOOKUP(G2,SUBSTITUTE($B$1:$D$6," ",""),3,0)是数组公式需要三键结束，按Ctrl+Shift+Enter得到对应结果

若查找值中包含空格，则公式为=VLOOKUP(SUBSTITUTE(G9," ",""),$B$8:$D$13,3,0)

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

目的：根据【销售员】查找【销售额】

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

说明：“查找值”F2也就是通过【销售员】查找【销售额】，由于查找区域中【销售员】列存在不可见字符，通过VLOOKUP函数常规计算会出现#N/A错误值

在这里需要将查找区域中不可见字符通过CLEAN函数进行去除

CLEAN函数：表示的是删除文本中不可见字符（非打印字符）

=CLEAN（字符串）

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

“查找区域”根据查找值【销售员】在所选的数据表，也就是CLEAN($B$1:$D$6)中必须位于第一列。“列序数”为3，表示对应结果【销售额】位于所选区域中排第3列，“匹配条件”为0，表示精确匹配。

最后注意由于当前公式=VLOOKUP(F2,CLEAN($B$1:$D$6),3,0)是数组公式需要三键结束，按Ctrl+Shift+Enter得到对应结果

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

目的：根据【工号】查找【销售员】、【地区】及【销售额】

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

说明：“查找值”$F2也就是通过【工号】查找【销售员】、【地区】及【销售额】，由于公式需要向右及向下填充，查找值列保持不变，需要将F列进行锁定也就是$F2

“查找区域”根据查找值【工号】在所选的数据表中，也就是$A$1:$D$6中必须位于第一列。

此案例中是多列批量查找“列序数”也就是所对应的结果值在不同的列中，需要嵌套一个COLUMN函数。

COLUMN函数：表示返回引用的列号

=COLUMN（单元格）

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

“匹配条件”为0，表示精确匹配

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

目的：根据【工号】查找【地区】、【销售员】及【销售额】

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

说明：“查找值”$F2也就是通过【工号】查找【地区】、【销售员】及【销售额】，由于公式需要向右及向下填充，查找值列保持不变，需要将F列进行锁定也就是$F2

“查找区域”根据查找值【工号】在所选的数据表中，也就是$A$1:$D$6中必须位于第一列。

此案例中是多列批量查找“列序数”并且与需要查找数据的字段名称与数据表中中的字段名称位置不一致，需要嵌套一个MATCH函数（源数据字段名称顺序是【销售员】【地区】【销售额】需要查找数据表中字段名称顺序是【地区】【销售员】【销售额】）

MATCH函数：表示返回指定数值在指定数组区域中的位置

=MATCH（查找值,查找区域,匹配条件）

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

“匹配条件”为0，表示精确匹配

第12种用法：一对多查找（COUNTIF）

目的：根据【地区】查找多名【销售员】

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

说明：此案例中各个地区有多名销售员，而VLOOKUP函数只能查找最近的数据，无法查找多个数据，需要在数据表中中增加一列【辅助列】，公式为=C2&COUNTIF($C$2:C2,C2)

COUNTIF函数：表示计算区域中满足给定条件的单元格的个数

=COUNTIF（区域，条件）

区域$C$2:C2：表示的是地区列，前面$C$2绝对引用，后面C2表示的是会随着公式向下填充，单元格区域累积增加

条件C2：表示的是指定的条件计算单元格个数

如果单纯的通过COUNTIF($C$2:C2,C2)计算得到的结果为1,1,2,1,2的数据，为了直观体现出各个地区的个数，在COUNTIF连接一个C2单元格，得到结果为杭州1，宁波1，杭州2，温州1，宁波1（这里的杭州2表示的是地区杭州有两个）

VLOOKUP函数查找值$G2&COLUMN(A1)根据数据表中辅助列的形式地区名+数字，也就是通过【地区】查找多名【销售员】（COLUMN函数请看VLOOKUP函数第10种用法说明）

“查找区域”根据查找值【辅助列】在所选的数据表中，也就是$A$1:$E$6中必须位于第一列，“列序数”为4，表示对应结果【销售员】位于所选区域中排第4列，“匹配条件”为0，表示精确匹配。

最后嵌套IFERROR（IFERROR函数请看VLOOKUP函数第5种用法说明）是因为温州地区只有一名销售员，通过公式填充会出现错误值，所以需要用IFERROR函数屏蔽错误值

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

目的：根据【消费金额】查找【赠品】

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

说明：“查找值”H2也就是通过【【消费金额】查找【赠品】

由于“查找区域”是有多个区域，需要用IF函数来判断是否是会员的情况，公式为IF(G2="是",$A$2:$B$5,$C$2:$D$5)

IF函数：表示的是根据指定的条件判断，当满足指定的条件返回一个值，不满足指定的条件返回相反的一个值

=IF（条件，满足条件的值，不满足条件的值）

此公式IF(G2="是",$A$2:$B$5,$C$2:$D$5)是根据条件是否是会员，当是会员时返回对应结果为【会员奖励规则】区域，当不是会员时返回对应结果为【非会员奖励规则】

“列序数”为2，表示对应结果【赠品】位于所选区域中排第2列，“匹配条件”为1，表示近似匹配。

最后嵌套IFERROR（IFERROR函数请看VLOOKUP函数第5种用法说明）是因为低于2000消费金额时没有赠品，通过公式填充会出现错误值，所以需要用IFERROR函数将错误值显示为“无赠品”，当然也可以直接输入一个英文的双引号，屏蔽错误值直接显示为空。

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

目的：根据【产品】查找每个月份的【销售额】

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

说明：“查找值”$B$1也就是通过【产品】查找每个月份的【销售额】，由于公式向下填充，查找值要绝对引用

此案例中“查找区域”在不同的工作表中，直接引用一个工作表中的数据区域，需要手动的更改工作表的名称会比较麻烦，工作效率也会很低。

在此可以引用【月份】列的数值，但是直接引用月份列的数值，公式为A2&"!A:B"得到的结果是错误（原因是A2&"!A:B"是文本，无法参与计算得到错误值）

出现这种情况，需要用INDIRECT函数来解决，公式为INDIRECT(A2&"!A:B")

INDIRECT函数：表示的是返回由文本字符串指定的引用（简单的理解为返回单元格的值）

=INDIRECT（单元格引用）

“列序数”为2，表示对应结果【销售额】位于所选区域中排第2列，“匹配条件”为0，表示精确匹配。

第15种用法：反向查找（数组或MATCH+INDEX）

目的：根据【销售员】查找【工号】

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

说明：“查找值”F3，也就是通过【销售员】查找【工号】，“查找区域”由于在查找的数据表中【销售员】列不在所选区域中排第一列（无法按照VLOOKUP函数的使用方法）。对于基础用法中讲解到增加辅助列可以查找对用的【工号】，现在无需增加辅助列结合IF函数数组用法可以实现反向查找，数组公式为IF({1,0},$B$2:$B$6,$A$2:$A$6)

第一参数：{1,0}，IF的第一参数是一个条件判断的逻辑值，现在却是{1,0}，在这里我们可以将1看作是True条件正确，将0看作是False条件错误，还需要注意的是1跟0是用大括号括起来的

当IF函数条件为1时，返回$B$2:$B$6单元格区域，也就是【销售员】区域

当IF函数条件为0时，返回$A$2:$A$6单元格区域，也就是【工号】区域

选中数组公式IF({1,0},$B$2:$B$6,$A$2:$A$6)按F9直观显示对应结果为{"洪宝坤","LM001";"凡克明","LM002";"曹锦荣","LM003";"周蒙","LM004";"郑欣宜","LM005"}

最后VLOOKUP函数“列序数”为2，表示对应结果【工号】位于所选区域中排第2列，就能得到对应的【工号】

注：数组公式需要三键结束，按Ctrl+Shift+Enter得到对应结果

若VLOOKUP函数结合IF函数数组公式难以理解，我们可以结合MATCH函数和INDEX函数查找对应的【工号】。

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

MATCH函数：表示返回指定数值在指定数组区域中的位置

=MATCH（查找值,查找区域,匹配条件）

此案例中MATCH(F2,$B$2:$B$6,0)根据F2单元格也就是查找【销售员】“曹锦荣”，在查找数据表中区域$B$2:$B$6，精确匹配，最后得到结果位置在第3行

INDEX函数：表示的是返回表格或区域中的值或值的引用

INDEX(区域，第几行，第几列)

此案例中INDEX($A$2:$A$6,MATCH(F2,$B$2:$B$6,0))，第一个参数根据指定【工号】区域$A$2:$A$6，第二个参数结合MATCH函数查找第3行对应结果的【工号】为“LM003”，第三个参数可忽略

第16种用法：多条件查找（数组或MATCH+INDEX）

目的：根据【地区】和【销售员】查找【销售额】

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

说明：“查找值”F2&G2，也就是通过【地区】和【销售员】查找【销售额】，“查找区域”IF({1,0},$B$2:$B$6&$C$2:$C$6,$D$2:$D$6)（IF函数数组说明请看VLOOKUP函数第15种用法），“列序数”为2，表示对应结果【销售额】位于所选区域中排第2列，“匹配条件”为0，表示精确匹配。

注：数组公式需要三键结束，按Ctrl+Shift+Enter得到对应结果

此案例也可以结合MATCH函数和INDEX函数查找对应的【销售额】。

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

MATCH函数和INDEX函数说明请看VLOOKUP函数第15种用法

注：数组公式需要三键结束，按Ctrl+Shift+Enter得到对应结果