▲扫码关注「秋叶 Excel」,回复【福利】有惊喜哟
说到函数,小伙伴们最常用的就是 VLOOKUP 了,她大大提升了我们的办公效率。但是 VLOOKUP 那小姑凉总爱闹脾气,乱点鸳鸯谱。
今天就跟我一起学习学习,用 VLOOKUP 帮助你找到对的那个人吧。
我们先了解一下 VLOOKUP 的语法:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
翻译一下就是:
=VLOOKUP(要查找的值、要在其中查找值的区域、区域中包含返回值的列号、精确匹配或近似匹配)
下面我们来说说这个小姑凉最爱闹的脾气之一,#N/A 错误。当函数或公式在数据区域中找不到查询对象时,会返回错误值#N/A。
通俗的话说就是:
下面我们就来看看,如何哄好小姑凉的这些小脾气吧。
症结 1
查找对象不在查找区域的第一列中
错误解析
VLOOKUP 查找对象必须位于查询区域的最左列。此例中「武汉分部」在 B2:D10 的第二列 ,所以返回# N/A 错误。
解决方法
修改 VLOOKUP 的第二参数区域和第三参数列号。
D14 单元格正确公式应为:
=VLOOKUP(C14,C1:D10,2,0)
症结 2
找不到完全匹配项
a. 数据类型不匹配
错误解析
在此例中,B 列的编号是文本格式,而 C14 是数字格式,数据类型不匹配 ,所以返回# N/A 错误。
解决方法
将 VLOOKUP 的第一参数转换为文本。
D14 单元格正确公式应为:
=VLOOKUP(C14&"",B1:D10,3,0)
延伸思考
问题 如果 B 列是数字,C14 是文本该怎么办呢?
解答 需要将第一参数转换为数值。
公式如下:
=VLOOKUP(--C14,B1:D10,3,0)
b. 有空格或者不可见字符等
错误解析
在此例中,B 列和 C14 的单元格式都是文本格式,但是为什么 VLOOKUP 还是返回# N/A 错误呢?
鼠标点击 C14 单元格,可以在编辑栏里看到「101」后面后两个空格。
解决方法
用 TRIM 函数删掉两端的空格。
D14 单元格正确公式应为:
=VLOOKUP(TRIM(C14),B1:D10,3,0)
延伸思考
问题 如果是 C14 单元格中是非打印字符(在编辑栏里也看不见的隐身字符),怎么办?
解答 使用 CLEAN 函数删除不能打印的字符 。
公式如下:
=VLOOKUP(CLEAN(C14),B1:D10,3,0)
c. 所见非所得
错误解析
在此例中,C 列的分部名称是通过自定义单元格格式将「武汉」化妆为「武汉分部」,通过编辑栏,可以看见 C2 单元格实际还是「武汉」,所以返回# N/A 错误。
解决方法
❶ 在 A 列添加辅助列,让所见变为所得。
❷ 在 A2 单元格输入公式:=C2&"分部",然后双击向下填充。
❸ 在 D14 单元格输入公式:
=VLOOKUP(C14,A1:D10,4,0)
症结 3
查找区域没有加绝对引用
错误解析
在此例中,由于没有对查询区域限定为绝对引用,D14 单元格公式向下填充时,查找区域发生变化,导致找不到查询对象,返回# N/A 错误。
解决方法
在 D14 输入公式时锁定查找区域,然后向下填充。
D14 单元格正确公式应为:
=VLOOKUP(C14,$C$1:$D$10,2,0)
症结 4
查找值并不完全匹配
错误解析
在此例中,C 列的分部名称包含「分部」两个字,而 C14 只有「武汉」,所以返回# N/A 错误。
解决方法
将 VLOOKUP 的第一参数使用通配符查询。
D14 单元格正确公式应为:
=VLOOKUP(C14&"*",C1:D10,2,0)
症结 5
数据源中没有需要的查找值
错误解析
严格的来说,这并不是 VLOOKUP 的错误,因为确实找不到对象嘛。但是为了数据处理的美观性,我们可以通过 IFERROR 函数辅助,在 VLOOKUP 查不到对象时返回空值进行处理。
解决方法
可以使用 IFERROR 函数屏蔽错误。
以上就是关于 VLOOKUP 小姑凉闹的#N/A 小脾气的案例,下一期我们说说她其他类型小脾气#REF!、#VALUE!~不见不散哟!
如果想要哄好这个爱发脾气的小姑凉,后台这个回复关键词【VLOOKUP】,尽情去 折磨 锻炼自己吧!
小 E 再啰嗦一句,最近一门 Excel 透视表实战新课上线了,备受职场老司机推崇、拯救表亲们工作效率的 Excel 透视表,究竟有什么样的魅力,扫描下面海报上的二维码了解一下吧~
秋叶 Excel
◆ ◆ ◆
在秋叶 Excel 中,我们特意制作了「精华文章分类宝典」供您查阅。宝典分类里,有近百篇详尽的教学文章,随时随地为你解决问题。
进入公众号,点击菜单栏中的【快速学习】,就能找到它啦。
▌关于本文
作者:Excel 研究院—李大饼
本文秋叶 Excel 原创发布,如需转载请在后台回复关键词「转载」查看说明
↓↓↓点击「阅读原文」,Excel 透视表新课上线啦,九月大促更优惠赶快了解一下吧!