Vlookup八种常见错误分析

Vlookup是大家最熟悉不过的一个函数了，但是很多初学者在使用这个函数时往往会遇到很多问题，从而得到了错误的结果。今天我们就来看看Vlookup使用过程中有哪些常见错误。知道了别人曾经遇到过的错误，我们自己在使用时才能避免犯同样的错误。

1、忽略绝对引用

这里有两点，一个是查找值，另外一个就是查找区域，这两个都需要注意不要忽略了相对引用和绝对引用的设置。

比如我们需要从以下格式的数据表中查询数据。

输入下面的公式并拖动复制填充公式时就会产生错误。

=VLOOKUP(H2,A2:E6,COLUMN(B1),0)

所以正确的公式应该是：

=VLOOKUP($H2,$A$2:$E$6,COLUMN(B1),0)

关于绝对引用、相对引用、混合引用的知识，请参考以下文章介绍。

这个小小的符号，没人敢忽视它！

2、忽略文本、数字格式的差异

文本型数字和真正的数值不一样，很多初学者都忽略了这一点。关于这个知识点，我在之前的文章中有过介绍，请点击以下链接学习。

Excel中这个坑，很多人都中招！

a. 查找值是文本，查找区域首列是数字

在以下示例中，某公司的物料编码是纯数字的，这常常会导致使用公式时的错误。以下是该公司部分产品的生产情况，其中“物料编码”一列是数字。

但是在查询表中，物料编码被设成了文本，如果直接应用Vlookup公式就会产生错误。

=VLOOKUP($G2,$A:$E,COLUMN(B1),0)

正确的方法是，将查找值乘以1转换成数值。公式如下。

=VLOOKUP($G2*1,$A:$E,COLUMN(B1),0)

另外，也可以通过“分列”的方式将数据源的“物料编码”转换成文本。

关于“分列”的方法，请参考以下文章介绍。

这么好用的功能一定要推荐给你！

b. 查找值是数字，查找区域首列是文本

这时我们还是有两种方法，其一，在公式中将查找值转换为文本；其二，使用分列的方法将查找区域首列中的文本转换为数值。将查找值转换为文本，可以使用&""的方法，请看如下示例。

=VLOOKUP($L2&"",$A:$E,COLUMN(B1),0)

3、查找值不在查找区域的首列

我们在示例中加上序号这一列，

以下是错误示例。

以下是正确示例。

查找区域应该从B列开始。

4、最后一个参数设置错误

第四个参数，如果是0或者False，则表示精确匹配，如果在查找区域首列中找不到值则返回错误值；如果是1或True，则表示模糊匹配，但是这往往达不到我们想要的结果。所以，我们第4个参数一般设置为0或False。

如下图所示，在查找区域中命名没有200000和800000这两个物料编码，公式还返回了查找结果，这并不是我们想要的。

5、第三个参数超出了查找区域的总列数

如下公式，查找区域只有5列，但是COLUMN(F1)=6，在5列数据中查找第6列数据，结果就返回错误值。

=VLOOKUP($G2,$A:$E,COLUMN(F1),0)

6、查找值或查找区域数据存在不可见字符

有时候我们从系统中导出来的数据可能会带上一些空格；有时从网页或者邮件中复制出来的数据中可能带着一些看起来像空格，但是使用“查找和替换”功能输入空格又替换不掉；更有甚者，有些字符中有不可见字符，但是通过逐个查看单元格中的字符时又找不到。这时，我们使用Vlookup时就需要先处理好这些数据了。

对于查找值、查找区域中的空格或者不可见字符，可以用Trim、Substitute去掉，也可以用“查找和替换”功能替换掉。

比如以下公式：

=VLOOKUP(SUBSTITUTE($G2," ",""),$A:$E,COLUMN(B1),0)

=VLOOKUP(TRIM($G3),$A:$E,COLUMN(B2),0)

7、2003版文件中的公式引用2007以上版本中整列数据

如下所示，在一个2003版的文件中（文件后缀.xls）使用公式，从2007版以上的文件中查找内容就会弹出以下错误提示。这是因为2003版的Excel工作表最大有65536行，而2007版之后就变成了1048576行。

8、通配符造成的错误

比如有以下分类统计的数据，在另外一个地方需要用Vlookup根据分类查询销售额。

下图中第一个公式没有处理查找值，所以返回错误结果。

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

第二个公式将~替换为~~，相当于强制声明~是一个有含义的字符，这样才能得出正确的结果。

=VLOOKUP(SUBSTITUTE(D3,"~","~~"),A:B,2,0)

Vlookup是一个常见的函数，大家都在用，但是一不小心可能就会出错。了解了这些常见的可能发生的错误，一方面我们可以防范出错，另一方便可以快速检查出错的公式并更正。

--End--

欢迎加入QQ群下载示例文件！

1群：9735376 （已满）

2群：426619302

aHR0cDovL3dlaXhpbi5xcS5jb20vci9oenVodVQ3RUJXTjZyWHJhOTI2Vg== (二维码自动识别)