Excel vlookup 函数的几种高难度错误及解决方案

在未升级到 O365 之前，vlookup 函数是最常用的查找函数之一，甚至对很多人来说没有之一。

那么重要的函数，然而在某些特定情况下却常常出错，可真闹心。

我归纳总结了一下，除了查询区域参数的相对、绝对引用容易搞错，第 3 参数容易对错列这种比较容易发现和解决的问题以外，以下是比较常见却又有点技术含量的问题。

案例：

  • 根据数字查找文本

  • 根据文本查找数字

  • 空值结果不要为 0

  • 带通配符“~”的查找

解决方案 1：根据数字查找文本

根据 D 列的月份查找出对应的获客数。

如果按正常思路，应该在 E2 单元格输入以下公式 --> 下拉复制公式：

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

但是由于 A 列的值是文本格式，而 D 列是数值，所以查找结果出错。

因此本例的公式需要做如下变通，就能成功查找出结果：

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

  • 本公式与之前的区别只是在第一个查找参数后面加了“&''”；

  • 作用是把查找的数字转换成文本型，使之可以成功与 A 列的文本匹配

解决方案 2：根据文本查找数字

下例的情况正好跟前面相反，A 列是数值，而作为查询列的 D 列却是文本格式。

还是先看一下中规中矩的 vlookup 用法是否可行。

在 E2 单元格输入以下公式 --> 下拉复制公式：

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

查找结果仍然出错，原因还是因为 A、D 两列的格式不匹配。

正确公式如下：

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

  • 该公式将查找参数 *1，这样就能把文本型数字转换成数值。

解决方案 3：空值结果不要为 0

下例中的 B 列有部分空值，用 vlookup 查询的时候，E 列会出现怎样的结果？

从下图可以看出，对于查询区域中的所有空值，vlookup 函数都返回 0。

但实际情况中，空值有时并不等同于 0，如何让空还是空？

只需要将以上公式变换为

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

  • 上述公式只是多连接了一个空格，就将 0 强制转化成了空值。

解决方案 4：带通配符“~”的查找

下图中的月份值为区间，月份之间用“~”连接。

A、D 列的格式完全一致，现在查找总该没问题了吧？

事实却未天从人愿。

这又是为什么呢？因为“~”是通配符，它的作用是将通配符“?”和“*”转换为普通字符。

如果要让“~”不具有通配符的作用，而是作为符号，那么就需要在它前面再加一个“~”。

因此在公式中套用一个 substitute 函数，将查找参数中的“~”替换成“~~”，就能成功找出结果。

=VLOOKUP(SUBSTITUTE(D2,'~','~~'),A:B,2,0)

有关带通配符查找的详解，请参阅 ​

赞 (0)

相关推荐