出错误并不可怕,怕的是不知道怎么解决。本文就教你破解VLOOKUP函数病症的良药。
▼
在总结VLOOKUP函数错误之前,我们先来回顾下VLOOKUP函数的语法:
=VLOOKUP(查找对象,查找范围,返回列数,精确匹配或者近似匹配)
了解了Vlookup函数的基本语法,可还是经常会遇到一些错误。于是满怀期待了走进了诊室,希望能够药到病除。
— 01 —
参数使用错误
NO.1:参数1使用错误
通常对于批量查找,参数1会使用引用单元格,这就涉及到引用方式的问题,稍微不注意,就可能造成查找错误。
比如,在F2输入公式=VLOOKUP($E$2,B:C,2,0),向下复制填充,结果三个单元格中返回的值都是89,显然高书记和李书记的成绩是错误的。
错误原因:第一个参数采用了绝对引用,这样在复制填充时,单元格引用不会变化,所以一直查找的是“陆亦可”对应的成绩。
解决方法:对于VLOOKUP的第一个参数,如果公式需要详细复制填充,则需要使用行的相对引用,所以公式改为
=VLOOKUP($E2,B:C,2,0)
或=VLOOKUP(E2,B:C,2,0)
NO.2:参数2首行错误
VLOOKUP函数中,参数2的限制很多,是最容易出错的地方。如图查找三位同事的成绩,结果均返回错误值。
错误原因:VLOOKUP函数的第二个参数是查找区域,这个区域不是随便选取的,它必须满足的条件之一就是:参数1(查找对象)必须位于该区域的第1列。
本例选取的参数2为A:C,其中姓名列位于此区域的第二列,所以无法正确查找。
解决方法:将公式修改为=VLOOKUP(E2,B:C,2,0),即函数的第二个参数设置为B:C。
NO.3:参数2区域范围错误
如图所示,参数2设置为B:C,可是查找还是出错了。
错误原因:参数2必须满足的条件之二就是:这个区域一定要包含需要返回的我们需要返回成绩,但是第2个参数B:C根本就没有包括D列的成绩。
解决方法:将公式修改为=VLOOKUP(F3,B:D,3,0),是第二参数包含需要返回的值。
NO.4:参数2区域中列的顺序错误
如图,使用姓名查找组别,结果返回错误值。
错误原因:本例中VLOOKUP函数的第二参数为A:B,其实这两有两个错误。一是参数1并不在参数2选定区域的第一列;第二是返回值“组别”在查找值的左侧,这是不允许的。
解决方法:①最简单的方法,将A、B列数据互换,然后再使用VLOOKUP查找;②使用index+match组合查找
NO.5:参数3使用错误
参数3“返回的列数”指的是在参数2这个区域中的列数,它不一定等于在Excel表格中的列数,如果将参数3设置为“4”,结果返回错误值。
错误原因:成绩位于参数2选定的区域“B:D”中的第三列,而不是第四列,因此参数3需要设置为3.
修改:将公式修改为=VLOOKUP(F3,B:D,3,0)即可。
NO.6:参数4设置错误。
如图所示,最后一个参数设置为1,结果返回的数值与姓名对不上号。
错误原因:VLOOKUP函数的参数4为0或省略时表示精确查找,非0值时表示模糊查找。这里设置为1,所以进行的是模糊查找。模糊查找,会找到和它最接近,但比它小的那个数。因此返回的数值不正确。
修改方法:将公式改为
=VLOOKUP(F2,B:D,3,)
或者=VLOOKUP(F2,B:D,3,0)
— 02 —
查找匹配的错误
除了参数设置错误之外,如果要查找的数据源不符合规范,也会出现错误。
NO.1:数据表中含有多余的空格。
如图查找成员的成绩,陆亦可的成绩能够正确查找,陈海的却出错。
错误原因:源数据中,为了使姓名对齐,在陈海姓名中间添加了空格。这样使用不带空格的“陈海”去匹配,当然查找不到了。
解决方法:使用TRIM或者手工删除空格。如果希望两个字的姓名与三个字的对齐,可以采用分散对齐的方式,如图所示。
NO.2:查找对象与源数据格式不一致
小白雷哥需要通过员工工号查询到电脑号码。使用=VLOOKUP(G3,A2:D12,4,1) 查询时,返回错误值#N/A 。
诊断分析:这是因为查找值(11208)与查找范围第一列(工号)数据格式不一致导致的。在vlookup函数查找过程中,文本型数字和数值型数字会被认为不同的字符。所以造成无法成功查找。
解决方案:将源数据中工号一列更改为文本类型,然后再查找。
— 03 —
单元格引用导致的错误
函数中的单元格引用,“混合引用”的方式是最复杂的,也是最容易出错的。不管是参数1,还是参数2都会有这样的问题。
如图使用COLUMN函数与VLOOKUP函数嵌套,一次返回多列查找值,在G2中输入公式
=VLOOKUP(F2,B2:D9,COLUMN(B1),0)
然后向下拖动复制,并向右拖动复制,但是返回的值有一部分是错误的。
错误原因:①由于第二个参数B2:D9是相对引用,所以向下复制公式后会自动更改为B3:D10,B4:D11……而F4中的陈海所在的行,不在B4:D11区域中,从而造成查找失败。
②而向右复制时,参数1会变为G3,因此查找对象变为了性别,从而造成查找失败。
解决方法:把参数2由相对引用改为绝对引用;参数1改为混合引用。即公式为=VLOOKUP($F2,$B$2:$D$9,COLUMN(B1),0)
这样既能确保向下复制时可以查找不同的姓名,又能确保向右复制时查找对象不会篡位。
这些VLOOKUP错误种类几乎囊括了所有的查找情况,如果碰到了错误,而你刚好有解决方案,效率岂不大增!
图文作者:安伟星
联系客服