首页 > Excel专区 > Excel教程 >

VLOOKUP函数查找技巧

Excel教程 2022-01-06 21:10:35

这篇文章中,我们将探讨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个参数进行适当的调整,就能够达到返回正确的数据的目的。


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

上一篇:FIND函数 下一篇:Excel VBA对象变量
office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.