如何更正 VLOOKUP 函数中的 #N/A 错误

本文介绍了可能导致 VLOOKUP 失败的最常见原因。

提示: 请参阅课程摘要卡:VLOOKUP 疑难解答提示,其中介绍了使用 VLOOKUP 时出现 #NA 的常见原因,并采用 PDF(可移植文档格式)文件呈现,非常方便。可与他人共享该 PDF 或将其打印出来供自己参考。

问题:查阅值不在 table_array 参数的第一列中。

VLOOKUP 最大的限制之一是它只能查找表格数组中最左列中的值。因此,如果查阅值不在数组的第一列,则会显示 #N/A 错误。

下表中,我们要检索 Kale 的销售量。

Vlookup 中的 #NA 错误:查阅值不在表数组的第一列中

出现该错误的原因是查阅值“Kale” table_array 参数 A2:C10 的第二列 (Produce),而 Excel 会查找 A 列,而不是 B 列。

解决方案:可以通过调整 VLOOKUP,使其引用正确的列来尝试解决此问题。如果不能,则可以尝试移动各列。当电子表格较大或较复杂,表格中的值是其他计算的结果时,或者因为其他逻辑原因而无法移动各列时,此方法非常难操作。解决方法是结合使用 INDEX 和 MATCH 函数,这样可以搜索查找表中任何位置的列中的值。

放弃 VLOOKUP,改为使用 INDEX/MATCH

VLOOKUP 无法满足需求时,可以使用 INDEX/MATCH。INDEX/MATCH 最大的优势在于可以搜索查找表中任意位置的列中的值。INDEX 根据位置返回指定表格/区域中的值,MATCH 返回表格/区域中的值的相对位置。在公式中结合使用 INDEX 和 MATCH 可以通过指定值在表格/数组中的相对位置来查找表格/数组中的值。

放弃 VLOOKUP,改为使用 INDEX/MATCH 具有以下几点好处:

  • 使用 INDEX 和 MATCH 时,返回值不必在查阅列中,而使用 VLOOKUP 时,返回值必须在指定区域内。这种差别有何影响?使用 VLOOKUP 时,必须知道返回值所在的列号。这听起来好像没什么大不了,但表格较大且非得计算列数时就会很麻烦。此外,如果要在表格中进行添加/删除操作,还必须重新计算和更新 col_index_num 参数。使用 INDEX 和 MATCH 时,查阅列和包含返回值的列不同,因此无需计算。

  • 使用 INDEX 和 MATCH 时,可以指定数组中的行或列,甚至同时指定行和列。这意味着可以同时在水平方向和垂直方向上查找值。

  • 可以使用 INDEX 和 MATCH 查找任何列中的值。使用 VLOOKUP 只可以查找表的第一列中的值,而 INDEX 和 MATCH 适用于查找第一列、最后一列或其他任何位置的值。

  • 使用 INDEX 和 MATCH 有利于灵活地动态引用包含返回值的列。这意味着你可以在表格中添加列,而 INDEX 和 MATCH 不会受到破坏。另一方面,如果非得在表格中添加列,VLOOKUP 会受到破坏,因为它对表格的引用是静态引用。

  • 使用 INDEX 和 MATCH 可以更灵活地查找匹配项。使用 INDEX 和 MATCH 可以查找与查阅值完全匹配、大于或小于查阅值的值。使用 VLOOKUP 只能查找与某值最接近(默认情况下)或完全匹配的值。VLOOKUP 还默认假定表格数组中的第一列按字母顺序排序,如果表格不是按这种方式设置的,VLOOKUP 将返回表中第一个最接近的匹配项,而这可能并不是你要查找的数据。

语法

若要构建 INDEX/MATCH 的语法,必须在 INDEX 中使用数组/引用参数,并在其中嵌套 MATCH 语法。如下所示:

=INDEX(数组或引用, MATCH(lookup_value,lookup_array,[match_type])

我们来使用 INDEX/MATCH 替换上述示例中的 VLOOKUP。语法如下所示:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

用简单的汉语表达其意思:

=INDEX(我希望在 C2 至 C10 单元格中查找返回值,返回值符合(Kale,它位于 B2 至 B10 单元格数组中,返回值是 Kale 对应的第一个值))

可以使用 INDEX 和 MATCH 函数替代 VLOOKUP

该公式在 C2 至 C10 单元格中查找 Kale( B7 单元格)对应的第一个值,并返回 C7 单元格中的值 (100),即与 Kale 匹配的第一个值。

问题:找不到完全匹配项

range_lookup 参数为 FALSE,并且 VLOOKUP 无法在数据中找到完全匹配项时,会返回 #N/A 错误。

解决方案:如果确定电子表格中存在相关数据,但 VLOOKUP 找不到该数据,请确保引用单元格中没有隐藏的空格或非打印字符。另请确保单元格遵循正确的数据类型。例如,包含数字的单元格的格式应该设置为“数字”,而不是“文本”。

使用 CLEANTRIM 函数清除单元格中的数据。

问题:查阅值小于数组中的最小值

如果 range_lookup 参数设置为 TRUE,并且查阅值小于数组中的最小值,则会显示 #N/A 错误。TRUE 会查找数组中的相近匹配项,并返回小于查阅值的最接近值。

在以下示例中,查阅值是 100,但 B2:C10 区域中不存在小于 100 的值,因此出现错误。

查阅值小于数组中的最小值时 VLOOKUP 中会出现 N/A 错误

解决方案

  • 根据需要更改查阅值。

  • 如果无法更改查阅值,但需要更灵活地查找匹配值,可考虑放弃 VLOOKUP,改为使用 INDEX/MATCH。使用 INDEX/MATCH 可以查找大于、小于或等于查阅值的值。有关放弃 VLOOKUP,改为使用 INDEX/MATCH 的详细信息,请参阅本文上一部分。

问题:查阅列未按升序排列

如果 range_lookup 参数设置为 TRUE,并且其中一个查阅列未按升序 (A-Z) 排列,则会显示 #N/A 错误。

解决方案

  • 将 VLOOKUP 函数更改为查找完全匹配项。为此,可将 range_lookup 参数设置为 FALSE。为 FALSE 时对排序没有要求。

  • 使用 INDEX/MATCH 函数查找未排序表格中的值。

问题:值是较长的浮点数

如果单元格中具有时间值或较长的小数,由于浮点精度,Excel 将返回 #N/A 错误。浮点数是小数点后的数字。(请注意,Excel 会将时间值存储为浮点数。)Excel 不能存储具有较长浮点数的数字,所以为了让函数正常工作,浮点数需要四舍五入到 5 个小数位数。

解决方案:缩短该数字,使用 ROUND 函数将其四舍五入到五个小数位数。

你是否有特定函数问题?

在 Excel 社区论坛中发布问题

帮助我们改进 Excel

是否有关于如何改进下一版 Excel 的建议?如果有,请查看 Excel User Voice 上的主题。

另请参阅

更正 #N/A 错误

VLOOKUP:消除 #NA

HLOOKUP、VLOOKUP、LOOKUP 在 Excel 中返回的值错误

在 Excel 中浮点运算可能会给出不准确的结果

课程摘要卡:VLOOKUP 复习

VLOOKUP 函数

Excel 中的公式概述

如何避免损坏的公式

使用错误检查功能检查公式中的错误

所有 Excel 函数(按字母顺序)

所有 Excel 函数(按类别)

扩展你的技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×