【excel】VLOOKUP跨表、跨文档查询、引用

摘自:https://support.microsoft.com/zh-cn/office/%e5%a6%82%e4%bd%95%e6%9b%b4%e6%ad%a3-vlookup-%e5%87%bd%e6%95%b0%e4%b8%ad%e7%9a%84-n-a-%e9%94%99%e8%af%af-e037d763-ffc3-4fae-a909-89c482d389b2?ui=zh-cn&rs=zh-cn&ad=cn 

在test.xlsx文档的'标签1'的E到K列中查询E3单元格,查找后把F列输出出来

从E向K数数,E是1,F是2,。。。

最后一个参数0FALSE表示精确匹配

=VLOOKUP($E3,'[test.xlsx]标签1'!$E:$K,2,0)

 

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

 

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

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

 

出现 #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 函数的第一个值) )

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

 

该公式在 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 的值,因此出现错误。

查阅值小于数组中的最小值时 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 技术社区中咨询专家或在 Answers 社区获取

posted @ 2022-03-28 00:47  LiuYanYGZ  阅读(1966)  评论(0编辑  收藏  举报