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，))