用VLOOKUP函数的12种常见错误解析

论坛 期权论坛 期权     
知否派   2019-7-13 06:02   4058   0
点击蓝字关注
后台回复“1
限时免费领取,实用EXCEL模板





1
函数参数使用错误

1、查找区域第一列需为必备条件。
例:如下图所示,根据姓名查找发放金额时产生错误。




解析:VLOOKUP函数规定查找区域的第一列必须为查找的必要条件。图中根据A9也就是姓名进行查找,因此A9所在列必须为查找区域的第一列,以此查找区域应为C2:D6。
正确公式为:=VLOOKUP(A9,C2:D6,2,0)

2、查找区域应包含需要查找列。
例:如下图所示,根据序号查找发放金额时产生查找错误。




解析:根据序号进行发放金额的查找,图中公式的查找范围为A2:C6,不包括我们需查找的发放金额的D列,因此产生了错误。
正确公式为:=VLOOKUP(A9,A2:D6,4,0)

3、公式第4个参数确实或设置错误。
例:如下图所示,根据序号查找姓名产生错误。




解析:VLOOKUP函数第四个参数为0时表示精确查找,为1或省略时表示模糊查找。如上图没有设置第四参数,因此公式会认为是模糊查找,因此序号没有“6”查找出来仍然是有对应姓名。
正确公式为=VLOOKUP(A9,A2:D6,3,0)
或  =VLOOKUP(A9,A1:D6,2,)
注:当参数为0时可以省略,但必须保留“,”号。

2
数字格式不同产生错误

4、查找为数字,被查找区域为文本型数字。
例:如下图所示,根据序号查找姓名,查找出现错误。




解析:在VLOOKUP函数查找过程中,要注意统一数字类型,需全部为文本型数字或者数值型数字。因为会被系统认为不同的字符导致查找数据失败。
解决方法:把查找的数字在公式中转换成文本型,然后再查找。
或者直接修改公式
=VLOOKUP(A9&"",A2:D6,3,0)

5、查找格式为文本型数字,被查找区域为数值型数字。
例:如下图所示根据工号查找姓名,查找出现错误。




解析:可以参考上一个例子。
解决方法:把文本型数字转换成数值型。
正确公式为:=VLOOKUP(A9*1,A1:D6,2,0)
注:如果你手工把文本转换成数值类型,就不必在公式中转换格式了。

3
引用方式使公式复制后产生错误

6、没有正确的使用引用方式,造成在复制公式后区域发生变动引起错误。
例:如下图所示,当B9的公式复制到B10和B11后,B10、B11公式返回错误值。




解析:由于B9公式的第二个参数A2:E6是相对引用,所以向下复制公式后会自动更改为A3:D7,而A10中的序号1所在的行,不在A3:D7区域中,从而造成查找失败。
解决方法:把第二个参数的引用方式由相对引用改为绝对引用即可,即锁定参数范围。
正确公式为:
=VLOOKUP(A9,$A$2:$D$6,2,0)


4
多余的空格或不可见字符

7、数据表中含有多余的空格。
例:如下图所示,由于A列工号含有多余的空格,造成查找错误。




错误原因:多一个空格,用不带空格的字符查找当然会出错了。
解决方法: 1 手工删除掉空格。建议用这个方法。
2 在公式中用trim函数替换空格而必须要用数据公式形式输入。
正确公式为:
=VLOOKUP(A9,TRIM(A2:D6),3,0)
按ctrl+shift+enter输入后数组形式为
{=VLOOKUP(A9,TRIM(A2:D6),3,0)}

8、类空格但非空格的字符。
在表格存在大量的“空格”,但又用空格无法替换掉时,这些就是类空格的不可见字符,这时可以“以其人之道还之其人之身”,直接在单元格中复制不可见字符粘贴到替换窗口,替换掉即可。







9、不可见字符的影响。
例:如下图所示的A列中,A列看上去不存在空格和类空格字符,但查找结果还是出错。




出错原因:这是从网页或数据库中导入数据时带来的不可见字符,造成了查找的错误。
解决方法:在A列后插入几列空列,然后对A列进行分列操作(数据 - 分列),即可把不可见字符分离出去。





10、VLOOKUP函数不支持反向查找产生的错误。
例:如下图所示的表中,根据姓名查找工号,结果返回了错误。




解析:VLOOKUP函数不支持反向查找。
解决方法:1 用if函数重组区域,让两列颠倒位置。
正确公式为:
=VLOOKUP(A9,IF({0,1},A2:A4,C2:C4),2,0)
2 用index+match组合实现。
正确公式为:
=INDEX(C2:C6,MATCH(A9,A2:A6,0),0)

11、通配符引起的查找错误。
例:如下图所示,根据区间查找提成返回错误值。




解析:~用于查找通配符,如果在vlookup公式中出现,会被认为特定用途,非真正的~。如在表格中查找3*6 ,356,376也被查找到。




如果精确查找3*6,需要使用~,如下图所示。




解决方法:用~~就可以表示查找~了。
正确公式为:
=VLOOKUP(SUBSTITUTE(A7,"~","~~"),A2:B4,2,0)


12、vlookup函数第1个参数不直接支持数组形式产生的错误。
例:如下图所示,同时查找张全蛋和李大仁的发放金额,然后用SUM求和。



解析: VLOOKUP第一个参数不能直接用于数组。
解决方法:利用N/T+IF结构转化一下数组。
正确公式为:
=SUM(VLOOKUP(T(IF({1},A9:B9)),A2:B6,2,0))




【Excel实践训练营】

“2019年搞定Excel”
Excel已经成为职场人必备的技能,30天系统训练,助你用Excel实现职场逆袭:
1、治疗你的懒癌,逼你真的学起来
2、听懂和做到,我们来实现
3、及时指点,解救你于错误之中
4、构建一个Excel学习共同体

详情扫描下方二维码了解:



分享到 :
0 人收藏
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

积分:15
帖子:3
精华:0
期权论坛 期权论坛
发布
内容

下载期权论坛手机APP