VLOOKUP函数查找技巧

这篇文章中，我们将探讨VLOOKUP函数的第1个参数，介绍一些查找方法和技巧。

情形1：查找数值的数据类型不一致

相同的值但以不同的数据类型来存储，对于VLOOKUP函数来说是不同的。

在单元格中，可以存储不同类型的数据，例如数字、文本字符串、日期和布尔值。在单元格中输入4000时，Excel通常将其识别并存储为数字。Excel默认右对齐数字。

有时，当从其他数据源导入数据到Excel中时，Excel会对数据类型进行假设，会将数字存储为文本字符串。Excel默认左对齐文本字符串。

图1

此时，如果使用VLOOKUP函数来匹配这两个值（一个值存储为数字，一个值存储为文本字符串），则不会匹配。当作为不同的数据类型存储时，VLOOKUP将不匹配等效值。如下图2所示，尝试查找编号对应的物品名称时，会返回错误。

图2

技巧：使用TEXT函数作为VLOOKUP函数的第1个参数

TEXT函数将数字转换为文本字符串。通过在VLOOKUP函数的第1个参数中使用TEXT函数，使查找值的类型匹配。

TEXT函数有两个参数，第1个参数是要转换的值，第2个参数是格式代码。因为我们不关心格式代码，所以对第2个参数使用0。

在图2中，查找编号对应的物品名称的公式修改为：

=VLOOKUP(TEXT(A11,0),表1,2,0)

显示正确的查找结果，如图3所示。

图3

当然，如果想要将数值文本转换成数值，可以使用VALUE函数。

更进一步，如果想要公式既满足数值文本，又适合数值，可以使用IFERROR函数：

=IFEEROR(VLOOKUP(TEXT(A11,0),表1,2,0),VLOOKUP(VALUE(A11,0),表1,2,0))

情形2：查找值在不同的列

有时，查找值不在同一列，如何使用同一公式来实现查找。

图4中灰色背景的单元格是要根据其左侧单元格值来获取相应的数据。

图4

在图5所示的表2中存储着原数据。

图5

使用VLOOKUP函数从表2中获取数据。在单元格D9中的公式：

=VLOOKUP(A9,表2,2,0)

结果如图6所示。

图6

然后，我们将公式复制到其他单元格中，如图7所示。可以看出，在单元格D14和D15中发生错误。

图7

很显然，出现错误的原因在于复制公式后，公式会自然地改变为查找引用单元格为A14和A15，如图8所示。而实际上要查找的单元格为B14和B15，即这里的查找值与原公式查找值在不同的列。

图8

一个简单的方法是，将公式中的A14修改为B14。然而，如果有许多这样的公式，修改起来很麻烦。能否使用同一个公式而无须修改呢？这样，公式更容易更新和维护。

技巧：在VLOOKUP函数的第1个参数中使用连接运算

通过连接值来创建单个文本字符串，其中一种方法是使用连接运算符&。修改上图6中的公式为：

=VLOOKUP(A9&B9,表2,2,0)

将公式复制到其他单元格中，结果如图9所示。

图9

情形3：查找值包含空格时

如果要查找的文本字符串包含前导空格、中间空格或尾空格，而在查找表中没有空格，那么VLOOKUP函数就会返回错误结果。

如图10所示，根据产品编号在表4中查找相应的成本。

图10

表4如图11所示。

图11

在图10中，单元格C10中的公式为：

=VLOOKUP(A10,表4,2,0)

结果返回错误值，如图12所示。

图12

为什么会这样？仔细检查，发现在单元格A10中的数据结尾包含有空格。

技巧：在VLOOKUP函数的第1个参数中使用TRIM函数

可以使用TRIM函数移除文本字符串中多余的空格。因此，将单元格C10中的公式修改为：

=VLOOKUP(TRIM(A10),表4,2,0)

将公式下拉至单元格C14，结果如图13所示。

图13

情形4：部分匹配

有时，查找的值只是查找表中数据的部分内容，查找表如下图14所示的表5。

图14

单元格A9中是查找值，要在单元格B5中返回查找的结果。使用公式：

=VLOOKUP(A9,表5,2,FALSE)

获得的结果为#N/A，如图15所示，

图15

当然，你可以使用我们前面介绍的技巧，将表5中的数据排序后再进行近似匹配，可能会返回所需要的结果。然而，我们这里使用更合理的部分匹配技巧。

技巧：在VLOOKUP函数的第1个参数中使用通配符

通配符是可以代表其他字符的一个字符。例如，星号（*）可以代表任意数量的字符。因此，我们需要将查找值与星号相连接。修改后的公式如下：

=VLOOKUP(A9&”*”,表5,2,FALSE)

结果如图16所示。

图16

在表中的数据后面包含查找值时，可以使用”*”&A9查找。在表中的数据中间包含查找值时，可以使用”*”&A9”*”。

结语

在使用VLOOKUP函数时，结合具体情形，将其第1个参数进行适当的调整，就能够达到返回正确的数据的目的。