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

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

本主题介绍VLOOKUP函数的错误结果的最常见原因, 并提供了有关使用索引匹配的建议。

提示: 此外, 请参阅课程摘要卡: VLOOKUP 疑难解答提示, 它们在方便的 PDF 文件中呈现 #NA 问题的常见原因。您可以与其他人共享 PDF, 或打印以供自己参考。

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

VLOOKUP 的一个约束是只能在表数组中最左侧的列上查找值。如果查阅值不在数组的第一列中, 您将看到 #N/a 错误。

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

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

#N/a 错误, 因为 lookup 值"Kale"显示在table_array参数 A2: C10 的第二列 (农产品) 中。在这种情况下, Excel 将在 A 列中查找它, 而不是列 B。

解决方案: 你可以尝试通过调整 VLOOKUP 来引用正确的列来修复此问题。如果不可能, 请尝试移动列。如果你有大型或复杂的电子表格, 其中单元格的值是其他计算的结果, 或者你不能在周围移动列的其他逻辑原因, 那么这也可能是高度 impracticable 的。解决方案是使用 INDEX 和 MATCH 函数的组合, 它可以在列中查找值, 而无需考虑其在查阅表格中的位置位置。请参阅下一节。

请考虑改为使用 INDEX/MATCH

对于 VLOOKUP 不能满足你的需求的许多情况, INDEXMATCH是很好的选择。索引/匹配的关键优势是, 您可以在查阅表中的任何位置的列中查找值。INDEX 根据其位置从指定的表/范围返回值。MATCH 返回表格/区域中值的相对位置。在公式中使用索引和匹配, 通过指定值在表/数组中的相对位置来查找表/数组中的值。

使用 INDEX/MATCH 而不是 VLOOKUP 有多个优点:

  • 借助 INDEX 和 MATCH, 返回值无需与查阅列位于同一列中。此值不同于 VLOOKUP, 在这种情况下, 返回值必须位于指定范围内。此操作有何影响?使用 VLOOKUP, 你必须知道包含返回值的列号。虽然这看起来可能不是挑战性的, 但是如果你有一个大型表, 并且必须对列数进行计数, 这会很麻烦。此外, 如果你在表中添加/删除列, 则必须重新统计并更新col_index_num参数。借助索引和匹配, 无需计数, 因为查阅列不同于具有返回值的列。

  • 使用 INDEX 和 MATCH, 你可以在数组中指定行或列, 或者同时指定这两者。这意味着你可以垂直和水平查找值。

  • INDEX 和 MATCH 可用于查找任何列中的值。与 VLOOKUP 不同, 在这种情况下, 你只能在表格的第一列中查找值, 如果你的查阅值位于第一列中的第一列、最后一个或两者之间的任意位置, 则 INDEX 和 MATCH 将起作用。

  • "索引和匹配" 提供了对包含返回值的列进行动态引用的灵活性。这意味着你可以将列添加到表中, 而不会中断索引和匹配。另一方面, 如果需要将列添加到表中, 则 VLOOKUP 会中断, 因为它会对表进行静态引用。

  • 索引和匹配通过匹配提供更大的灵活性。"索引" 和 "匹配" 可以找到完全匹配的值或比查阅值更大或更小的值。VLOOKUP 仅查找与值最接近的值 (默认情况下) 或精确值。vlookup 还假定表格数组中的第一列按字母顺序排序, 假设您的表格未按这种方式进行设置, 但 VLOOKUP 将返回表中的第一个最接近的匹配项, 这可能不是您要查找的数据。

语法

若要为索引/匹配生成语法, 需要使用 INDEX 函数中的数组/引用参数, 并将匹配语法嵌套在其中。这将采用以下形式:

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

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

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

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

= INDEX (返回 C2 中的一个值: C10, 它位于 B2 中的某个位置 (Kale), 其中的 return 值是与 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 错误

解决方案

  • 根据需要更改查阅值。

  • 如果不能更改查阅值并需要更大的值, 而不是使用匹配的值, 请考虑使用 INDEX/MATCH 而不是 VLOOKUP (请参阅本文上方的部分)。通过索引/匹配, 你可以查找大于、小于或等于查找值的值。有关使用 INDEX/MATCH 而非 VLOOKUP 的详细信息, 请参阅本主题中的上一节。

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

如果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 上的主题。

另请参阅

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

此信息是否有帮助?

谢谢您的反馈!

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

×