Excel Home
全球知名的Excel资源网站之一

VLOOKUP出错?排查手册请收好

昨天在ExcelHome知识星球里看到一位同学的提问,在使用VLOOKUP函数时,明明查询区域里有对应的数字,却返回了错误值。

这个问题老祝觉得比较有代表性,下面咱们就结合这个问题,共同学习一下VLOOKUP出错时可能存在的原因,小伙伴们遇到这样的问题时,可以顺藤摸瓜,对照这些原因来排除错误。

先打开出现问题的文件看看:
要实现的目的,是希望根据F列的商品名称,在左侧的数据源中查询出对应的出库单价。

先看看G2单元格中的公式:
=VLOOKUP(F2,B2:D14,3,FALSE)

第一参数,也就是查询值,是F2单元格中的商品名称,这个没问题。
第二参数,也就是查询区域,是数据源所在的B2:D14单元格区域,而且这个区域的首列也包含了要查询的商品名称。这个好像也没问题。
第三参数,也就是要返回哪一列的内容,这里写成3,是希望从B2:D14这个区域中返回第三列的出库单价信息,这个也没问题。
第四参数,用FALSE来指定使用精确匹配的查询方式,这个也没问题。

再看看G3单元格中返回错误值的公式:
=VLOOKUP(F3,B3:D15,3,FALSE)
咦,这里的查询区域怎么变成了从第三行开始?

哈哈哈,问题就这样轻松解决了,是因为第二参数没有使用绝对引用,当公式向下复制时查询区域就变了。
如果要查询的内容在数据源的前几行,而查询的数据范围下拉后不再包含这几行,那就肯定会出现#N/A错误。
只要将第二参数变成绝对引用,再向下复制公式,查询区域始终固定就OK:
=VLOOKUP(F2,$B$2:$D$14,3,FALSE)

除了查询区域没有使用绝对引用之外,VLOOKUP出错的常见原因还包扩以下几种:

1、单元格里有空格

如下图中,公式写法没问题,引用方式也没问题,出错的原因极有可能是被查询的内容里有空格,或者查询区域的单元格里有空格了。

 

解决方法是按Ctrl+H键调出【查找和替换】对话框,在【查找内容】文本框中输入空格,点【全部替换】按钮。

实际操作时,可以在英文输入状态下输入空格,全部替换,然后再在中文输入状态下输入空格,继续替换一次。

 

2、不可见字符

如果是从系统导出的数据源,这个问题存在的概率比较大。
解决方法是单击可能包含不可见字符的列标,在【数据】选项卡下点【分列】按钮,然后在弹出的对话框中直接点【完成】即可。
这种方法能清除大部分类型的不可见字符。

 

3、查询区域选择错误

如下图所示,要查询的商品名称,在数据源是B列,如果将公式写成从A列开始,那就不能怪VLOOKUP了:
=VLOOKUP(F2,$A$2:$D$14,3,FALSE)

 

4、漏掉了第四参数

第四参数用于指定使用哪种匹配方式,如果省略参数值,仅以逗号占位,或者将参数值写成0,作用和使用FALSE一样的,都是精确匹配。
但是如果省略了参数值,逗号又给漏掉,那就别怪VLOOKUP不客气了。
就像下图,公式直接返回一个错误结果,你说这有多坑人:

 

5、数字格式不一致

这种问题,主要出现在数字类的查询中。
看看下图中的公式:
=VLOOKUP(D2,A:B,2,0)

D列的编码是文本型的数字,而A列查询区域的编码是常规格式的数值,所以在查询时就有问题了。
解决方法是把查询区域的格式和被查询的内容统一成同一种格式。
可以使用两种方法处理,一是修改公式,将查询值乘以1,使其变成数值:
=VLOOKUP(D2*1,A:B,2,0)

还有一种方法是使用分列,将A列的编码变成文本格式。
对于已经输入的内容,不能通过设置数字格式的方法实现从文本到数值格式的互相转换,所以要使用分列功能,相当于重新输入了一次。

最后还有一种可能出现问题的原因,就是查询内容中包含“*”或是“~”,这两个符号有特殊身份,在查询条件中出现时,会被Excel当成通配符处理。
简单有效的处理方法是将数据源以及查询内容中的“*”和“~”使用其他符号来替代,这个问题在实际工作中不多见,咱们知道有这么回事就可以啦。

赞(141)
未经允许不得转载:ExcelHome » VLOOKUP出错?排查手册请收好
分享到

关于我们联系我们
本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号

征信