如何快速排查并解决VLOOKUP查询结果的报错

众所周知，VLOOKUP是EXCEL中非常经典的查询函数之一。

       

VLOOKUP官方版本说明

我们经常会用到他帮助匹配两张表的数据，尤其是在数据量非常庞大的时候，只要写下一个标准格式公式，按下回车，脑子都不用动一动，就能够轻轻松松让计算机自动完成工作了。

然而，尽管大多数情况下，VLOOKUP都能帮我们顺利完成工作，但偶尔也会罢工。明明公式很正确，但就是显示错误。看着屏幕上的结果——"#N/A"——相当闹心。

真是让人又爱又恨的函数。

怎样才能快速排查掉VLOOKUP的错误？

一般错误出现在在两方面：

  1. 参数设置错误
  2. 关键字错误

下面将以这位名叫32135兄弟(临时昵称小三)的典型性案例，一步一步进行说明排查步骤。

小三有两张表，其中表1有数据200行，表2数据有800行。

       

匹配两张表

他想根据关键字A列，将表2连接到表1中，试了很多次，始终不成功，显示N/A报错，问题在哪里？

第一步：检查公式参数设置是否正确

       

vlookup官方用法说明

根据office官方给出的VLOOKUP函数使用说明，概括参数设定如下：

而小三兄弟的公式为：

VLOOKUP(A2,'[表2.xlsx]Sheet1'!$1:$800,2)

观察参数设定：

我们发现标红底白色的两部分，有两处并未按照要求进行设定。

  • 错误一：

数据区域单元格地址，只指定了行号，并没有指定列号，所以系统会认为指定区域不明确，拒绝执行公式运算，因此会报错;

表哥Tips:

如果数据区域没有指定行号，但是指定了列号，系统会默认为在指定列数内从第一行到最后一行全部都是指定区域。可以执行公式运算。

  • 错误二：

根据案例的要求，需要精确匹配关键字，所以要设定为精确匹配参数”FALSE”。

于是将公式修改为:

VLOOKUP(A2,'[表2.xlsx]Sheet1$A'!$1:$A$800,2,FALSE)

如果公式和链接的表名都是正确的，如果仍然报错N/A，怎么办呢？

第二步：检查两张表关键字列是否一致

检查两个方面：一是检查关键字的内容，二是检查关键字的格式

  • 检查一：是否只有个别关键字无法匹配

具体做法是，下拉填充全部公式。

如果其他行可匹配，则说明此项在表2中无相关匹配项，需要去补充表2的数据；

如果全部都匹配不上显示"#N/A"，则说明关键字两边不一致，需要人工复查数据，调整关键字保持一致。

观察表1，尽管有大部分显示"#N/A"，但仍然有个别行是可以从表2匹配进来的。小三人工检查过，那些没有匹配进来的项目，在表2中确实是可以找到相关数据的。

所以不需要对表2进行补充。

为何关键字没有错，却仍然找不到匹配项？

  • 检查二：是否两张表关键字格式不同

经了解，原来表1红圈圈中匹配过来的数据，是由于小三无意识的操作。

他以为是关键字两边不匹配，所以从表1复制了关键字到表2，敲了一下回车键，就自动匹配到了表1中。

难道真的是关键字内容不同吗？

当然不。聪明的你看到标题也猜到是格式的问题。

没错，正是两张表关键字的格式不一致，才是造成表2无法匹配到表1真正原因。

观察表2的关键字列，会发现在每个单元格左上角有一个绿色的小三角，而观察表1是没有的。有绿色三角的单元格是文本格式，无法参与计算。因此，要将表2的关键字单元格改为数值形式。

你是不是以为接下来做的是"全选第一列，调整单元格格式为数值形式"？

如果你是这样想，结果会令人失望的。

常规修改格式的办法，并不能解决这个问题。

刚才在上面提到，小三无意中，复制了一个单元格进来，敲了回车，转换成了数值，就可以运算了。

一共800行，难道要一个个复制敲回车吗？

亚！美！爹！

(码字累了，请允许表哥皮一下O(∩_∩)O~)

       

感叹号快速转换文本为数值

因为没有原表，所以表哥自己做个小例子示意。

选中需要修改格式的关键字单元格区域(注意不是全选)，会出现黄底黑色感叹号，点击旁边下拉三角，选择"转换为数字"，即可瞬间批量修正。

表哥Tips:

在感叹号中修改是最快捷的修改格式的方法。也可采用以往文章讲的提取数字的方法解决。

以上就是VLOOKUP排查错误的步骤和方法。

你学会了吗？

↖(^ω^)↗撒花

赞 (0)

相关推荐