艾巴生活网

您现在的位置是:主页>科技 >内容

科技

vlookup函数的12种常见错误_vlookup函数查找12种易犯错误

2024-08-22 11:34:56科技帅气的蚂蚁
vlookup功能是一个非常有用的搜索功能,但是由于种种原因,在实际使用中会遇到各种令人困惑的错误。所以本文将对经常遇到的vlookup错误进行

vlookup函数的12种常见错误_vlookup函数查找12种易犯错误

vlookup功能是一个非常有用的搜索功能,但是由于种种原因,在实际使用中会遇到各种令人困惑的错误。所以本文将对经常遇到的vlookup错误进行整理,希望对同学们有用。

一、使用函数参数类型1的错误:设置第二个参数区域的错误之一。

举例:如下图所示,根据姓名查找年龄时出错。

错误原因:vlookup函数的第二个参数是搜索区域,这个区域的第一列有一个前提条件,就是被搜索的对象(A9)必须对应区域的第一列。在本例中,搜索是基于名称的,因此第二个参数名称必须在区域的第一列中,上面公式中的名称列在区域a1: E6的第二列中。所以公式应该改成:

=VLOOKUP(A9,B1:E6,3,0)

类型2:第二个参数区设置不正确。

例2如下图所示,按名称搜索作业时出现搜索错误。

错误原因:这个例子是按名字找工作,但是请注意第二个参数B1:D6根本不包括E列的工作,当然会出错。所以公式应该改成:

=VLOOKUP(A9,B1:E6,4,0)

类型3:第四个参数缺失或设置不正确。

例3,如下图所示,根据工号找到名字。

错误原因:vlookup的第四个参数为0时表示精确搜索,为1或省略时表示模糊搜索。如果忘记设置第四个参数,会被公式误认为故意遗漏,会按照模糊搜索进行。当区域不符合模糊搜索规则时,公式将返回一个错误值。所以公式应该改成。

=VLOOKUP(A9,A1:D6,2,0)

Or=VLOOKUP(A9,A1:D6,2,)注意:当参数为0时,可以省略,但必须保留“,”符号。

二、不同的数字格式造成第四种搜索错误:搜索的是数字,搜索的区域是文字数字。

例4:按如下图工号查名字,搜索有错误。

错误原因:在vlookup函数搜索过程中,文本数字和数字数字会被认为是不同的字符。所以,不可能成功找到。

解决方法:将搜索到的数字转换成公式中的文本类型,然后重新搜索。即:

=VLOOKUP(A9"",A1:D6,2,0)

第五种:搜索格式为文字数字,搜索区域为数字数字。

例5:按如下图工号查名字,搜索有错误。

错误原因:同4。

解决方案:将文本数字转换成数字。即:

=VLOOKUP(A9*1,A1:D6,2,0)

三、复制公式后,参考方法导致错误。第六,引用方法使用不正确,导致公式复制后面积发生变化,产生误差。

例6,如下图所示,将C9的公式复制到C10和C11时,C10公式返回错误值。

错误原因:由于第二个参数A2:D6是相对引用,公式复制下来后会自动改为A3:D7,A10工号A01所在行不在A3:D7区域,导致查找失败。

解决方案:只需将第二个参数的引用模式从相对引用改为绝对引用。

B9公式改为=VLOOKUP(A9,$ A $2: $ D $6,2,0)。

四、多余空格或不可见字符7:数据表包含多余空格。

如下图所示,示例7导致了一个搜索错误,因为A列的工单编号包含多余的空格。

错误原因:如果多了一个空格,用不带空格的字符搜索肯定是错误的。

解决方案1、手动替换空格。推荐使用这种方法;

2、公式中使用trim函数替换空格,但必须以数据公式的形式输入。

即:=VLOOKUP(A9,TRIM(A1:D6),2,0)按ctrl shift enter,数组形式为{=VLOOKUP(A9,TRIM(A1:D6),2,0)}。

类型8:类似空格但不是空格的字符。

当表格中有大量的“空格”,但又不能用空格代替时,这些就是像空格一样的隐形字符。这时你可以“以其人之道还治其人之身”,直接在单元格中复制隐形字符粘贴到替换窗口中,然后进行替换。

类型9:隐形人物的影响

示例:下图所示的A列,A列中没有空格和类空格字符,但搜索结果仍然错误。

错误原因:这是从网页或数据库导入数据带来的不可见字符,导致搜索错误。

解决方法:在A列后插入几个空列,然后分隔A列(数据分隔)来分隔不可见字符。

类型10:反向查找vlookup不支持的生成错误。

例10在下图所示的表格中,根据名称搜索工号,结果返回错误。

错误原因:vlookup不支持反向查找。

解决方案1、用if函数重新组织区域,使两列颠倒。

=VLOOKUP(D8,IF({0,1},D2:D4,E2:E4),2,0)

2、通过索引匹配组合实现。

=INDEX(D2:D4,MATCH(D8,E2:E4,0))

类型11:通配符导致的查找错误

如下图所示,示例11根据区间搜索佣金返回一个错误值。

错误原因:~用于查找通配符。如果出现在vlookup公式中,会被认为是特殊用途,不真实~。比如查表3 * 6,356,376也是找到的。

如果精确找到3*6,就需要用~,如下图。

解决方法:用~ ~表示找到~。因此,该公式可以修改如下

=VLOOKUP(替换(A8,"~","~~",A2:B4,2,0)

类型vlookup函数的第一个参数不直接支持以数组形式生成的错误。

例12,如下图所示,同时求乘积A和C的和,然后用SUM求和。

错误原因:VLOOKUP的第一个参数不能直接用于数组。

解决方法:使用N/T IF结构对数组进行变换,如果不知道N/T IF结构的用法。

公式修改为:

=SUM(VLOOKUP(T(IF({1},A8:B8)),A2:B5,2,))