Applies ToMicrosoft 365 专属 Excel Microsoft 365 Mac 版专属 Excel Excel 网页版 Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel for Windows Phone 10

本主题介绍函数错误结果的最常见 VLOOKUP 原因,并提供改用 INDEXMATCH 的建议。

提示: 此外,请参阅快速参考卡:VLOOKUP 疑难解答提示,它在一个方便的 PDF 文件中提供了导致 #NA 问题的常见原因。 可与他人共享该 PDF 或将其打印出来供自己参考。

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

VLOOKUP 的一个约束是它只能在表数组中最左侧的列中查找值。 如果查找值不在数组的第一列中,就会显示 #N/A 错误。

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

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

#N/A 错误结果,因为查找值“Kale”出现在 table_array 参数 A2:C10 的第二列 (Produce) 中。 在这种情况下,Excel 会在 A 列而不是 B 列中查找它。

解决方案:可以通过调整 VLOOKUP,使其引用正确的列来尝试解决此问题。 如果不可能,则尝试移动列。 当电子表格较大或较复杂,并且表格的单元格值是其他计算的结果,或者由于其他逻辑原因导致无法移动列时,该方法可能非常不切实际。 解决方法是结合使用 INDEX 和 MATCH 函数,这样可以搜索查找表中任何位置的列中的值。 请参阅下一部分。

请考虑改用 INDEX/MATCH

对于 VLOOKUP 无法满足需求的许多情况,INDEXMATCH 是很好的选择。 INDEX/MATCH 的主要优势在于可以在位于查找表任何位置的列中查找值。 INDEX 根据指定表/范围的位置从表/范围中返回一个值。 MATCH 返回值在表/范围中的相对位置。 在公式中结合使用 INDEX 和 MATCH 可以通过指定值在表/数组中的相对位置在表/数组中查找该值。

使用 INDEX/MATCH 而不是 VLOOKUP 具有以下几个优点:

  • 使用 INDEX 和 MATCH,返回值不必与查找列位于同一列中。 这与 VLOOKUP 不同,因为 VLOOKUP 中的返回值必须在指定范围内。 这种差别有何影响? 使用 VLOOKUP 时,必须知道返回值所在的列号。 虽然这似乎并不困难,但如果表格较大且必须计算列数时就会非常麻烦。 另外,如果在表中添加/移除列,还必须重新计数并更新 col_index_num 参数。 使用 INDEX 和 MATCH 时,查阅列和包含返回值的列不同,因此无需计算。

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

  • 可以使用 INDEX 和 MATCH 查找任何列中的值。 这不同于 VLOOKUP,因为使用 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 错误

解决方案

  • 根据需要更改查阅值。

  • 如果无法更改查找值,但需要更灵活地查找匹配值,可考虑使用 INDEX/MATCH 而不是 VLOOKUP - 请参阅本文上面的部分。 使用 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 技术社区中咨询专家或在社区中获取支持。

另请参阅

需要更多帮助?

需要更多选项?

了解订阅权益、浏览培训课程、了解如何保护设备等。

社区可帮助你提出和回答问题、提供反馈,并听取经验丰富专家的意见。