Excel常用函数之VLOOKUP函数排错 / 开普饭

从近期与大家的交流看，还有很多朋友对VLOOKUP函数不是很熟悉。因此，准备详细的介绍一下这个函数的使用。VLOOKUP函数功能非常强大，使用场景也变化很大，因此准备分为几篇来介绍：

今天是第二篇，VLOOKUP函数排错。主要内容有：

VLOOKUP公式的错误

关于VLOOKUP函数，我被问到最多的问题，就是出现错误怎么办：

基本上，遇到的错误就是#N/A。（实际上，还有找错了的情况，参见上一篇文章。不过大部分同学遇到的都是#N/A）

在这个例子中，我们要查找的条件是姓名为“黄药师”，但是在查找区域的第一列中，并没有“黄药师”，当然找不到，就会返回#N/A。

实际上，VLOOKUP函数返回#N/A的唯一原因就是在查找区域的第一列中找不到查找值。但是，在实际情况中，造成这种现象的原因很多，我们需要仔细排查。

下面是我推荐的VLOOKUP排错步骤。

VLOOKUP函数排错步骤

一旦你的VLOOKUP函数返回了一个#N/A，你可以按照如下的步骤错误排查。

检查查找区域的第一列是否是你想要的查找条件

例如，在下图中，我们要在右侧列表中查找洪七公对应的主要功夫，但是为什么返回值是#N/A？

实际上，是因为，我们选定的区域是从H列开始的，而这个区域的第一列是H列，不是姓名，而是ID：

当我们选定的查找区域只是其所在区域的一部分时，这种情况经常发生。不仅仅是对于新手，对于很多老手来说，这种情况也很常见。

检查第二个参数是否为绝对引用

在下图中，我们要根据姓名查找主要功夫，为什么黄蓉的就能够查出来，而郭靖的却返回#N/A

原因就是在写黄蓉的公式时，第二个参数使用了相对引用：I5:L10，这样，随着公式往下拖拽，郭靖的公式变成了I6:L11，在这个区域中，郭靖被排错在外了。

只要将第二个参数变成绝对引用就可以了。

检查两边的单元格格式是否一致

有时候，错误是因为格式不同造成的：

明明两边大批u皮2020/5/3的日期，为什么找不到呢？

原来是因为一个日期是日期格式，另外一个是文本：

检查是否有空格

在VLOOKUP所有的#N/A错误中，绝大多数是由于空格造成的：

明明在表中有黄蓉，为什么却找不到呢？

这是因为B5单元格有空格：

通过简单的Len函数就可以判断这两边看上去一模一样的两个黄蓉是否真正一致：一个长度为3，一个长度为2，所有不一致，有一个包含一个空格。

我们可以使用TRIM函数去掉空格，或者通过替换去掉空格。实际场景中，两边都可能有空格，所以都需要做处理。

检查是否有不可打印字符

不可打印字符也可以造成VLOOKUP的错误：

仍然是黄蓉，但是却找不到，通过LEN函数计算长度发现确实不一致：

但是，这里不是空格造成的，是不可见字符造成的：

从编辑栏上，很明显可以看到左边有个空格，而右边却看不到多余的字符。

多余的不可见字符可以用CLEAN函数来处理。

其他情况

除了上述情况外，在很稀有的场景中，可以有其他字符造成VLOOKUP函数找不到匹配结果。这种情况有各种原因，可以联系我们帮助处理。

VLOOKUP函数的其他错误

除了#N/A外，VLOOKUP函数遇到的错误最多的是#REF!，

同样是查找黄蓉，但是返回了错误值：#REF!。

注意，一般来说，这个结果表示匹配成功了（即找到了黄蓉所在的行），但是需要返回的列不见了：

END

Excel常用函数之VLOOKUP函数排错