如何更正 VLOOKUP 函数中的 #N/A 错误
如何更正 VLOOKUP 函数中的 #N/A 错误
本主题介绍函数的错误结果的最常见的VLOOKUP原因,并提供改用INDEX和MATCH的建议。
提示: 另外,请参阅课程摘要卡: VLOOKUP 疑难解答提示,可在方便的 PDF 文件中呈现 #NA 问题的常见原因。 可与他人共享该 PDF 或将其打印出来供自己参考。
问题:查阅值不在 table_array 参数的第一列中。
VLOOKUP 的一个约束是它只能在表数组中最左侧的列上查找值。 如果您的查阅值不在数组的第一列中,您将看到 #N/A 错误。
下表中,我们要检索 Kale 的销售量。
N/A 错误,因为 lookup 值"Kale"显示在table_array参数 A2: C10 的第二列(农产品)中。
在这种情况下,Excel 将在列 A 中查找它,而不是列 B。
解决方案:可以通过调整 VLOOKUP,使其引用正确的列来尝试解决此问题。 如果不可能,请尝试移动您的栏。 这也可能是高度 impracticable 的,如果您有大型或复杂的电子表格,其中单元格值是其他计算的结果,或者您不能在周围移动列的其他逻辑原因。 解决方法是结合使用 INDEX 和 MATCH 函数,这样可以搜索查找表中任何位置的列中的值。 请参阅下一节。
请考虑改为使用 INDEX/MATCH
当 VLOOKUP 不能满足您的需要时,索引和匹配是很好的选择。 INDEX/MATCH 的关键优点是,您可以在查阅表中的任何位置的列中查找值。 INDEX 根据其位置从指定的表/区域中返回值。 MATCH 返回表/区域中值的相对位置。 在公式中使用 INDEX 和 MATCH,通过指定表/数组中的值的相对位置来查找表/数组中的值。
使用 INDEX/MATCH (而不是 VLOOKUP)有多种好处:
-
通过 INDEX 和 MATCH,返回值不必与查阅列在同一列中。 这与 VLOOKUP 不同,后者中的返回值必须位于指定范围内。 这种差别有何影响? 使用 VLOOKUP 时,必须知道返回值所在的列号。 虽然这看起来可能不太难,但当你有一个大型表并且必须对列数进行计数时,这可能会很麻烦。 此外,如果在表中添加/删除列,则必须重新统计并更新col_index_num参数。 使用 INDEX 和 MATCH 时,查阅列和包含返回值的列不同,因此无需计算。
-
使用 "索引和匹配",你可以在数组中指定一行或一列,或者同时指定二者。 这意味着可以同时在水平方向和垂直方向上查找值。
-
可以使用 INDEX 和 MATCH 查找任何列中的值。 与 VLOOKUP 不同,在这种情况下,你只能在表的第一列中查找值,如果你的查阅值位于第一个列、最后一个或中间的任意位置,则索引和匹配将起作用。
-
"索引和匹配" 提供了对包含返回值的列进行动态引用的灵活性。 这意味着您可以将列添加到表中,而不会中断索引和匹配。 另一方面,如果需要向表中添加列,则 VLOOKUP 中断,因为它会对表进行静态引用。
-
索引和匹配通过匹配提供更大的灵活性。索引和匹配可以找到完全匹配的值,或大于或小于查找值的值。 使用 VLOOKUP 只能查找与某值最接近(默认情况下)或完全匹配的值。 VLOOKUP 还默认假定表格数组中的第一列按字母顺序排序,如果表格不是按这种方式设置的,VLOOKUP 将返回表中第一个最接近的匹配项,而这可能并不是你要查找的数据。
如何优化找不到数据 #N/A 错误
N/A 错误通常表示公式找不到要求查找的内容。
热门解决方案
VLOOKUP、HLOOKUP、LOOKUP 或 MATCH 函数的 #N/A 错误的最常见原因是公式找不到引用值。
例如,源数据中不存在查阅值。
在此情况下,查阅表格中未列有“香蕉”,因此 VLOOKUP 返回 #N/A 错误。
解决方案:
可确保源数据中存在查阅值,或在公式中使用 IFERROR 等错误处理程序。
例如,=IFERROR(FORMULA(),0),即:
- = IF(公式求值错误,显示 0,其他情况显示公式的结果)
可使用“”不显示内容,或替换你自己的文本:=IFERROR(FORMULA(),”Error Message here”)
结尾
事后分析这么"简单"的一个问题,我们3个Excel文盲,每个人搞了半个小时都没搞定...
仔细回想一下,为什么会出现这样的结果?
1.觉的太简单了.
以至于,我们还是觉得,随便搜索一个答案,
复制一个公式,改一下参数,就ok了...
答案搜索到了,公式复制过来了,但是我们用的时候确总是提示=>"#N/A"
2.不够细心,抄错了答案
搜索出来了答案,答案也提到了这个规则,
"#N/A 错误,因为 lookup 值"Kale"显示在table_array参数 A2: C10 的第二列(农产品)中。 "
VLOOKUP函数用于搜索指定区域内首列满足条件的元素...
还是要仔细阅读答案,好好抄答案.
如果非要想一下,为什么抄答案都这么不仔细,可能还要回归到上一个原因上,
觉得太简单了,觉得抄一个公式就行了.
如果非要吐槽一下答案的话,
这个答案将一个重要的规则,一言带过...
最好最后列出几项,注意事项.
比如:
注意事项
- 匹配内容书写错误
- 使用的数据源区域错误;如果存在此分享中下拉情况,请使用$(shift+4)进行锁定:检查数据源是否有绝对引用
- ...
更糟心的是,搜索出答案"excel中vlookup函数结果出现#n/a的解决办法",也没有提到这个规则.