花了20个小时整理的VLOOKUP的12种经典用法与6种报错原因

关于VLOOKUP的各种用法与报错的原因，老师在花费了将近20个小时才整理出了这份资料，相信会对大家使用VLOOKUP有更大的帮助。

1

VLOOKUP的12种用法

名称：VLOOKUP函数

功能：搜索区域内满足条件的元素，确定待检索单元格在区域中的序号，再进一步返回选定单元格的值。

语法：

VLOOKUP(lookup_value,rable_array,col_index_num,[range_lookup])

相信看了上面的介绍，大家也没有看明白是什么意思，翻译成人话就是：

=VLOOKUP(找谁，所在的区域找，第几列，精确查找还是模糊查找）

01

常规查找

查找姓名对应的销售额。在F3单元格中输入公式：

=VLOOKUP(E3,$A$2:$C$9,3,0)，按Enter键完成。如下图所示：

02

日期查找

在查找日期的时候查找的结果通常会是一串数字，为了使日期能够返回相应的格式，那么需要配合TEXT函数才能完成查找需求。在F3单元格中输入公式：

=TEXT(VLOOKUP(E3,$A$2:$C$9,2,0),"yyyy/m/d")，按Enter键完成。如下图所示：

注：如返回格式为2018/12/03，则TEXT的第二个参数的格式可以设置为“yyyy/mm/dd”即可。

03

查找时值为空

在当查找的值为空时，通常情况下会返回结果为0，那么如果让结果返回空白呢，解决的方法就是在公式后面一个“”。

在F3单元格中输入公式：=VLOOKUP(E3,$A$2:$C$9,3,0)&""，按Enter键完成。如下图所示：

01

当查找的目标格式不统一时报错如何解决

（1）如果查找的目标值是文本格式，而数据区域中是数值格式。

如下图所示，A列中的员工编号为数值格式，而F3单元格中的员工编号为文本格式。在G3单元格中输入公式：=VLOOKUP(--F3,$A$2:$D$9,4,0)，按Enter键完成。

注：--为两个负号，即减负的意思，可以理解为负负得正，这里是把文本强制转换为数值，所以问题就很容易被解决了。

（2）如果查找的目标值是数值格式，而数据区域中是文本格式。

如下图所示，A列中的员工编号为数值格式，而F3单元格中的员工编号为文本格式。在G3单元格中输入公式：=VLOOKUP(F3&"",$A$2:$D$9,4,0)，按Enter键完成。

注：&""是强制地把数值格式转换成文本格式。

05

区域查找

有时候需要查找某一个值处于那个区间里。比如查找下列的销售额对应的销售提点为多少。在E2单元格中输入公式：=VLOOKUP(D2,$H$2:$I$8,2,1)，按Enter键完成。

注：这里使用该函数最后一个参数为1，即模糊查找，来确定查找的值处于给定的那一个区间。

06

模糊查找

VLOOKUP函数也是支持模糊查找，即支持通配符查找。如果还不懂通配符的小伙伴们可以查找文章《Excel中的通配符,你使用过嘛，这次算是说清楚了！》。

查找姓名中带有“冰”字的员工的销售额，在H3单元格中输入公式：

=VLOOKUP("*"&G3&"*",$B$2:$D$9,3,0)，按Enter键完成。

注：如果要查找以“冰”开头的那么公式的第一参数为："*"&G3; 如果查找以“冰”结尾那么公式的第一个参数为：G3&"*".

07

查找顺序与数据区域中顺序一致的多项时

VLOOKUP函数查找顺序一致的多项时，可以借助COLUMN函数构建查找序列。在H2单元格中输入公式：

=VLOOKUP($G2,$A$2:$D$9,COLUMN(B1),0)，按Enter键后向右填充。

注：COLUMN函数是返回列号。第一个参数一定要锁定列号，这样才能正确的结果。

08

十字交叉查询

VLOOKUP函数如果有两个条件是呈现十字交叉时且顺序与数据区域中的顺序不一致时，可以与MATCH函数完成查询。在H2单元格中输入公式：

=VLOOKUP($G2,$A$2:$D$9,MATCH(H$1,$A$1:$D$1,0),0)，按Enter键完成后向下向右填充。

注：一定要锁定VLOOKUP函数的第一个参数的列号，MATCH函数的第一个参数的行号，这样才能得到正确的结果。

09

多条件查询

VLOOKUP还能进行多条件查询，这个用法相信有很多人不知道吧。

在I2单元格中输入公式：

{=VLOOKUP(G2&H2,IF(,$A$2:$A$9&$B$2:$B$9,$D$2:$D$9),2,0)}

按组合键完成后向下填充。

注：公式两边的花括号不是手动输入的，而是按组合键后自动输入的。VLOOKUP的第三个参数为2，第四个参数为0是固定的。

10

反向查找

VLOOKUP函数也可以进行反向查找。在H2单元格中输入公式：

{=VLOOKUP(G2,IF(,$B$2:$B$9,$A$2:$A$9),2,0)}，

按组合键键完成后向下填充。

注：公式两边的花括号不是手动输入的，而是按组合键后自动输入的。

或者也可以使用下面的方法：在E2单元格中输入公式：

=VLOOKUP(D2,CHOOSE(,$B:$B,$A:$A),2,0)

按Enter键后向下填充。

11

一对多查询

VLOOKUP函数还能进行一对多查询，但是这个方法并不鼓励大家去使用。

在H2单元格中输入公式：

{=VLOOKUP($G$2&ROW(A1),IF(,$A$2:$A$9&COUNTIF(INDIRECT("a2:a"&ROW($2:$9)),$G$2),$D$2:$D$9),2,0)}，

按组合键完向下填充。

注：公式两边的花括号不是手动输入的，而是按组合键后自动输入的。

12

多表汇总

如下图所示，是某个部门半年的人员的工资与补贴的表，每个表里的人员的顺序是不一样的。

现要求将每个人的各个月的补贴汇总至一个表中，如下图所示：

在汇总表里的C2单元格中输入公式：

=VLOOKUP($A2,INDIRECT("'"&C$1&"'!B:G"),6,0)

然后按Enter键完成后向下向右填充。如下图所示：

注意：$A2是表示将员工编号这列的列号锁定，即在向右填充的时候不会使纵向的位置发生变化；

INDIRECT("'"&C$1&"'!B:G")如是将每个工作表的引用方式表示出来，INDIRECT函数可将字符串表示中动态的引用范围；这里说明一下，标准 跨工作表的引用的格式为：'工作表名'!单元格地址，如'销售-01月'!B:G

同时在C$1的时候一定要将其行号锁定，不然会在下拉的时候位置发生改变导致结果错误。需要强调的是书写公式的时候标点符号是英文状态半角的。

最后使用VLOOKUP函数将其查询出来即可。

2

VLOOKUP出错的原因及解决方法

01

参数使用错误

查找下面的右边的内容对应的销售额。如下图所示：

错误：查找结果与实际不符合。

原因：VLOOKUP的最后一个参数有两种选择，一种是0（表示精确查找），另一种是1（表示模糊查找）。所以上述的公式应该修改为：=VLOOKUP(F3,$B:$D,3,0)，最后一个参数也可以省略，但是逗号不能省略。

02

格式不统一

查找公司代码对应的欠款金额。如下图所示：

错误：查找结果出现了错误值。

原因：A列的公司代码为数值型，F列为文本型，所以查找时格式不统一出现了错误。公式应该修改为：=VLOOKUP(--F3,$A:$D,4,0)。

03

引用范围未锁定

查找公司代码对应的欠款金额。如下图所示：

错误：查找结果出现了错误值。

原因：由于查找的数据源区域是未锁定的，在向下填充的过程中数据源会出现随之变化的情况，所以就出现了错误。公式应该修改为：=VLOOKUP(F3,$A$2:$D$7,4,0)。混合引用的切换的快捷键为。

04

空格或者非可见字符

查找在时候如果目标与引用区域不一致，如下图所示：

错误：姓名列与查找目标列有空格不一致。

原因：由于查找的目标区域或者目标值不统一，有空格或者不可见的字符，所以就出现了错误。

如果有空格，公式应该修改为：=VLOOKUP(TRIM(G2),$B$2:$D$9,3,0)；

如果有不可见字符，公式修改为：=VLOOKUP(CLEAN(G2),$B$2:$D$9,3,0)

05

引用区域出错

查找姓名对应的销售额。如下图所示：

错误：查询结果为错误值。

原因：姓名在左边的数据区域中是第2列，所以VLOOKUP的第二个参数应该从第二列开始。公式修改为：=VLOOKUP(TRIM(G2),$B$2:$D$9,3,0)。

06

特殊作用的字符

查找错误出现错误，公式也没有错。如下图所示：

错误：查询结果为错误值。

原因：这里的“~”的特殊的作用，起了通配符的作用，要想查到正确的结果，需要解除通配符，即将这里的“~”替换成“~~”，公式可修改为：：

=VLOOKUP(SUBSTITUTE(H2,"~","~~"),$B$2:$E$7,4,0)