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

本文主要介绍函数上错误结果的最常见 VLOOKUP 原因，并提供有关改为使用 INDEX 和 MATCH 的建议。

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

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

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

出现 #N/A 错误，因为查找值 "Kale" 出现在 (参数 A2：C10) 的第 二 table_array列。 在这种情况下，Excel 在列 A 而不是列 B 中查找它。

解决方案：可以通过调整 VLOOKUP，使其引用正确的列来尝试解决此问题。 如果无法实现，请尝试移动列。 如果具有较大或复杂的电子表格，其中单元格值是其他计算的结果，或者可能还有其他逻辑原因导致无法四处移动列，则这可能也高度不可行。 解决方法是结合使用 INDEX 和 MATCH 函数，这样可以搜索查找表中任何位置的列中的值。 请参阅下一部分。

请考虑改为使用 INDEX/MATCH

INDEX 和 MATCH 是许多 VLOOKUP 不能满足需求的情况的不错选项。 INDEX/MATCH 的主要优点是可以在查找表的任何位置的列中查找值。 INDEX 根据其位置返回指定表/范围中的值。 MATCH 返回表/范围中值的相对位置。 通过指定表/数组中值的相对位置，在公式中将 INDEX 和 MATCH 一起用于查找表/数组中的值。

使用 INDEX/MATCH 而不是 VLOOKUP 有几个好处：

• 使用 INDEX 和 MATCH 时，返回值不需要与查找列在同一列中。 这不同于 VLOOKUP，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 函数中的 array/reference 参数，并嵌套函数中的 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) ) 的第一个值

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

问题：找不到完全匹配项

当 range_lookup参数为 FALSE 且 VLOOKUP 无法在数据中查找完全匹配项时，它将返回 #N/A 错误。

解决方案：如果确定电子表格中存在相关的数据，但 VLOOKUP 未捕获该数据，请花些时间验证引用的单元格是否没有隐藏空格或非打印字符。 此外，请确保单元格遵循正确的数据类型。 例如，数字单元格的格式应设置为"数字"，而不是"文本"。

此外，请考虑使用 CLEAN 或 TRIM 函数清理单元格中的数据。

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

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

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

解决方案：

• 根据需要更改查阅值。

• 如果无法更改查找值，并且需要更大的匹配值灵活性，请考虑使用 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 函数将其四舍五入到五个小数位数。

如果大家有其他的方法，或者想要知道哪方面的办公技巧，下方评论哦~

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；