Fork me on GitHub

在Excel中用vlookup公式灵活查找数字或文本类型关键字

2019-12-28 中文

技术

Excel vlookup

VLOOKUP公式里的查找值必须和被查找区域的首列关键字为相同类型,否则会返回“#N/A”,如果查找值是引用过来的,类型不同,此时可以强行转换类型,即可使VLOOKUP公式生效

想用关键字在多列数据的Excel表里找对应数据,公式怎么写?

假设你在A1到B6范围内有一个姓名和性别的对应列表,你想在D2单元格输入姓名,在E2单元格得到性别,那么E2的公式可以按下图这样写。在E2输入 =VLOOKUP(D2,A1:B6,2,FALSE)

vlookup01
图1

解析:
VLOOKUP公式一共需要4个参数,前3个是必填的,第4个是选填的。

  1. 第一个参数是“查找值”

它也就是你想用来查找的关键字,在查找的时候要用这个关键字去匹配被查找的数据区域的第一列,从中找到匹配的那一项。假如你的关键字是姓名,那么你要确保被查找的数据区域内“姓名”是在左边第一列,而如果性别在左,姓名在右,那么用普通的VLOOKUP写法就找不到它了。这时可以搜一下“VLOOKUP反向查找”,太长不说。

  1. 第二个参数是被查找的数据区域

本次被查找的数据处在A1到B6单元格范围内,其实你输入“A1:B6”或者“A2:B6”效果都是一样的,因为第一行是标题行,可以不要。
如果不想精确到哪一行,可以用“A:B”,那么被查找的数据范围就会扩大到整个两列,而不只是这12个单元格。

  1. 第三个参数是用户想显示的数据在被查找的数据区的第几列

本次我们的数据区域只有两列,“性别”在第2列,如果我们想显示“性别”,那么就输入2,而如果我们想显示被查找的关键字自己,输入1就可以了。
此处要注意,这个 2 是返回值相对于整个数据区域的第几列,如果数据区域在C1到D6,仍然是2列,“性别”仍然在第2列,那么虽然D是整个Excel的第4列,我们还是要输入2。

  1. 第四个参数是布尔值,是问用户要不要模糊查找

“布尔值”就是TRUE和FALSE,如果这里不填,默认为TRUE,那么就会进行模糊查找匹配,可能你要找张涵予,而列表里没有张涵予,但是有张馨予,那就给你匹配到张馨予那里了,这样就不太对了。
总之建议你写上FALSE就行了。

* 如果有两个张涵予,一个是男的,一个是女的,那么返回哪个人的性别呢?
答:返回第一个出现的张涵予的性别,如果是女的在前面,那就返回女的。

* 如何让单元格直接显示 =VLOOKUP(D2,A1:B6,2,FALSE)
答:可能会有人想知道怎样让单元格直接显示 =VLOOKUP(D2,A1:B6,2,FALSE) 而不是计算结果,其实有两种办法。

  1. 把单元格格式改成“文本”,然后双击单元格再回车即可。
  2. 在单元格内输入="=VLOOKUP(D2,A1:B6,2,FALSE)"即可。

* 那么反过来,如果单元格内输入了公式却不显示计算结果只显示公式本身,那怎么办?
答:把单元格格式改成“数字”,然后双击单元格再回车即可。

VLOOKUP公式明明是对的,为什么返回了“#N/A”?

假如我们现在不用汉字而是用纯数字做查找值,那么会怎么样呢?看看下面的2个例子吧!

vlookup02
图2

vlookup03
图3

数据区域范围、查找值单元格位置、公式什么的全都跟之前是一样的,为什么都会返回一个错误呢?

这里要注意了,图2里面的数据区域的“学号”的单元格左上角有绿色小三角,且Excel默认给它们居左了,这说明从A2到A6的单元格格式为“文本”。我们再看用来查找的关键字D2,它左上角没有绿色小三角,且Excel默认给它居右了,这说明它的单元格格式为“数字”,两边的数据格式不一致。当我们用“数字”作为关键字去匹配“文本”格式的数据时,是找不到结果的,所以公式计算的结果就报错了。

再看图3,数据区域的“学号”的单元格左上角没有绿色小三角,且Excel默认给它居右了,这说明从A2到A6的单元格格式为“数字”。我们再看用来查找的关键字D2,它左上角有绿色小三角,且Excel默认给它们居左了,这说明它的单元格格式为“文本”,又是出现了数据格式不一致的情况,所以也是找不到的。

例1有两种解决办法:

  1. 选中A2到A6,Excel会提示一个黄色的感叹号按钮,点击感叹号可以批量将文本格式的数字改成数字格式。
  2. 给D2的数字前面加个英文的单引号

例2也有两种解决办法:

  1. 选中A2到A6,右击选中区域,点“单元格格式”,改成“文本”,但是还需要一个一个双击单元格再回车来确认输入,所以不推荐
  2. 把D2单元格里数字前面的英文单引号删掉

数据区域和查找值不能修改,如何让VLOOKUP正确返回数据?

想要解决上面的问题,但又不能直接改数据区域和查找值,那么我们可以对公式作如下的修改

vlookup04
图4

vlookup05
图5

在VLOOKUP公式当中,如果我们希望查找值D2从数字转为“文本”,可以在查找值的位置输入 D2&"",这样就能在不更改D2的情况下,强行把VLOOKUP公式内的查找值变成“文本”类型。

而如果我们希望查找值D2从文本转为“数字”,可以在查找值的位置输入 D2+0,这样就能在不更改D2的情况下,强行把VLOOKUP公式内的查找值变成“数字”类型。

* 使用 +0 时必须确认原数据是纯数字,否则会报错

以上~