为什么 VLOOKUP 有时会失败

VLOOKUP 很可靠，但也会以几种容易预料的方式失败。了解相关规则后，大多数错误都不再神秘。

本课将介绍查找失败的常见原因，以及每种问题的确切修复方法。掌握这些内容后，令人困惑的 #N/A 和 #REF! 错误就能快速、轻松地修复。

VLOOKUP 只能搜索其 table_array 的最左列，并返回右侧的值。它无法查找一个值，再返回该值左侧的内容。

如果 ID 位于 C 列，而您想要的名称位于 A 列，VLOOKUP 就无法向左查找。您可以重新排列列，将搜索列放在最前面；也可以使用 INDEX-MATCH 或 XLOOKUP，因为它们可以向任意方向搜索。

列索引编号是从 table_array 的左侧开始计算的，而不是从工作表开始计算。一个常见错误是直接使用工作表的列字母作为编号。

如果您的范围是 C1:F10，并且想要 F 列，那么它是该范围的第 4 列，因此索引应为 4，而不是 6。从错误的一侧开始计数会返回错误的字段；如果编号超出范围宽度，则会返回 #REF! 错误。

如果列索引编号大于 table_array 中的列数，VLOOKUP 就会返回 #REF!。

例如，对于三列范围 A1:C10，要求返回第 5 列是不可能的：

您可以扩大 table_array，使其包含所需的列；也可以将索引改为范围内实际存在的列编号。

省略第四个参数时，默认值为 TRUE（近似匹配）。在未排序的列表中，这会悄无声息地返回错误的相邻值，而不是错误提示，因此很难察觉。

修复方法很简单，而且应该养成习惯：进行精确查找时，始终添加 FALSE。

查找值 "A100" 不会与末尾带有空格的 "A100 " 匹配。导入的数据中充满了这类不可见的差异。

表现是：某个值明明存在，您却得到 #N/A。使用 TRIM 清理两侧的内容，以移除多余空格：

如果您的查找值是数字 100，但表格将代码存储为文本 "100"（或反过来），两者就不会匹配，并且会得到 #N/A。

请查找表示文本的绿色小三角或左对齐数字。转换即可修复：将文本放入 VALUE() 中，使其变为数字；或者使用 &"" 将空字符串与数字拼接，使其变为文本，从而让两侧具有相同的类型。

如果忘记锁定 table_array，向下复制公式时，范围就会偏离数据。第 2 行的 A1:C100 会变成第 3 行的 A2:C101，接着变成 A3:C102，从而遗漏部分行。

使用绝对引用即可修复：这样表格保持不变，只有查找值会发生偏移：

每种错误都指向一种原因：

• #N/A - 未找到该值（不匹配、存在空格、类型错误，或该值确实缺失）
• #REF! - 列索引编号大于范围，或者引用的单元格已被删除
• #VALUE! - 某个参数的类型错误，例如列索引为负数或零
• #NAME? - 函数名称拼写错误，例如 VLOOKP

将错误与其含义对应起来，问题就已经解决了一半。

调试时，您还可以将查找公式包裹起来，让用户看到清晰的提示，而不是原始错误。IFERROR 会捕获任何错误，并返回您指定的文本。

这并不能修复根本原因，因此请在了解查找失败原因后再使用它。过早隐藏错误可能会掩盖真实的数据问题。

查找行为异常时，请快速检查以下各项：

• 搜索值是否位于范围的第一列？
• 列索引编号是否从范围的左侧开始计算，并且没有超出范围宽度？
• 是否添加了用于精确匹配的 FALSE？
• 两侧是否具有相同的类型（文本或数字），并且没有多余的空格？
• 是否使用美元符号锁定了 table_array？

从上到下检查这份清单，几秒钟内就能解决绝大多数查找失败问题。

诊断这个失败的查找公式。

常见原因及其修复方法：

• 左列限制 - 重新排列列，或使用 INDEX-MATCH / XLOOKUP
• 列索引编号错误或超出范围 - 从范围的左侧开始计数；扩大范围
• 缺少 FALSE - 对 ID 始终设置精确匹配
• 空格以及文本与数字类型不一致 - 使用 TRIM 清理，使用 VALUE 或 &"" 进行转换
• 未锁定表格 - 使用 $ 让范围保持不变

读取错误代码，将其与原因对应起来，然后应用修复方法。现在，您已经拥有一套完整的工具，可以进行可靠的查找。