Excel VLOOKUP 错误

2025年03月17日 | 阅读 9 分钟

MS Excel，即 Microsoft Excel，是一款强大的电子表格程序，它使用户能够记录数据并使用多个单元格内的函数和公式执行各种操作。VLOOKUP 是现有的 Excel 函数之一。它允许我们搜索并返回整个工作簿中的任何其他列的数据。然而，如果我们不熟悉该函数，尤其是在直接在单元格中使用它时，该函数可能会显得很棘手。如果我们错误地应用 VLOOKUP 函数，它会产生各种错误，或者可能返回错误的结果。

本文讨论了我们在 Excel 中工作时经常遇到的各种 VLOOKUP 错误。由于 VLOOKUP 错误，我们会得到不正确的结果或特定的错误代码，表明所应用的公式有问题。

Excel 中常见的 VLOOKUP 错误有哪些？

在 Excel 中，VLOOKUP 是一个广泛使用的函数，用于从各种数据源提取特定值。然而，该函数也有一些局限性。当我们不遵守 VLOOKUP 的某些限制或规范时，通常会导致意外的结果和错误。这就是为什么许多 Excel 专家认为 VLOOKUP 是最复杂的 Excel 函数之一。

以下是使用 Excel 中的 VLOOKUP 函数时经常出现的几个最常见的错误：

VLOOKUP #N/A 错误
VLOOKUP #NAME? 错误
VLOOKUP #REF! 错误
VLOOKUP #VALUE! 错误

为了避免 VLOOKUP 错误，我们必须确保使用格式正确的数据，并且没有空单元格、格式不正确的值和已删除的公式。此外，我们可以将 IFERROR 函数与 VLOOKUP 一起使用。它有助于忽略错误并返回放在错误位置的任何内容。

现在让我们详细讨论每个 VLOOKUP 错误，并了解如何相应地修复它们。

VLOOKUP 中的 #N/A 错误

当公式找不到所需的查找值来生成结果时，通常会出现 VLOOKUP 中的 #N/A 错误。N/A 表示“不可用”。此错误可能由于以下原因之一而发生：

1. 查找值拼写错误

led

在处理大型数据集时，我们可能会输入拼写错误的查找值。在这种情况下，VLOOKUP 公式会返回 #N/A 错误，因为它在定义的表范围内找不到拼写错误的该值。当处理大型数据集时，我们可能会输入拼写错误的查找值。在这种情况下，VLOOKUP 公式会返回 #N/A 错误，因为它在定义的表范围内找不到拼写错误的该值。

在上表中，公式正在搜索值“Speaker”，而表范围中输入的值是“Speakers”。公式找不到精确匹配，因为查找值少了一个“s”。纠正查找值中的拼写错误可以修复 #N/A 错误。

2. 精确匹配 VLOOKUP 中的 #N/A 错误

当我们应用 VLOOKUP 搜索精确匹配（range_lookup 参数为 FALSE）时，如果在定义的表范围内找不到精确匹配，公式将返回 #N/A 错误。

在上表中，公式正在搜索 ID 号 100（单元格 D4 中的值），返回 #N/A 错误，因为范围中的最小 ID 号是 101。公式找不到精确匹配。提供正确的匹配查找值可以修复 #N/A 错误。

3. 近似匹配 VLOOKUP 中的 #N/A 错误

当我们应用 VLOOKUP 搜索最接近或近似匹配（range_lookup 参数为 TRUE）时，公式会在以下两种情况下返回 #N/A 错误：

定义表（查找数组）中的值列未按升序排序。

在上表中，记录 ID 的顺序是混杂的。尽管定义范围中存在值 100，但公式无法查找 range_lookup 参数设置为 TRUE 的值，从而导致 #N/A 错误。这是因为 A 列或 ID 未按升序排序。将范围列按升序排序可以修复 #N/A 错误。
查找/搜索值小于范围内写在范围内的最低值。

在上表中，公式在查找数字 100（单元格 D4 中的值）时返回 #N/A 错误，即使设置为查找近似结果。此错误在此表中的原因是值（100）小于范围中的最低值（101）。使用近似匹配 VLOOKUP 时，我们不应查找较小的值。但是，当查找值大于范围中定义的该值时，公式可以正常工作。

4. 查找列不是最左边的列

VLOOKUP 中 #N/A 错误的常见原因之一是，提供的表数组或范围不包含位于其左侧的查找列。LOOKUP 公式无法从其左侧返回值。因此，查找列在表数组中必须始终结构化在最左侧。我们通常会忘记 VLOOKUP 公式这一限制，从而得到相应的错误。

将查找列移动到定义表数组的最左侧可以修复 #N/A 错误。将列移动到最左侧后，我们必须再次指定数组表或范围。

如果由于某些原因无法切换列或重新组织数据以使查找列成为最左边的列，则应避免使用 VLOOKUP 函数。相反，我们可以将 INDEX 和 MATCH 函数结合起来作为 VLOOKUP 的替代方案。

5. 数值格式设置为文本

单元格的格式在 VLOOKUP 中起着重要作用。如果我们的数据集包含格式为文本的数字，无论是在主表还是查找表中，VLOOKUP 公式通常会返回 #N/A 错误。这通常发生在我们将数据从其他源复制或导入而不是手动输入时。此外，以撇号开头的数字会被 Excel 解释为文本。

如果我们的工作表中有文本格式的数字，则会为这些单元格显示错误指示器。如果我们选择此类单元格，Excel 也会显示相应的消息。

将文本格式的数字转换为数字通常可以修复 #N/A 错误。要将存储为文本的数字转换为精确数字，我们必须首先选择所有这些有问题的单元格/数字。之后，我们需要单击错误图标，然后从列表中选择“转换为数字”选项。

6. 前导或尾随空格

VLOOKUP 中 #N/A 错误的另一个常见原因是存在一个或多个额外的空格，无论是单元格值的前面还是后面。在处理 VLOOKUP 中的空格时，可能存在以下两种情况：

查找值中的额外空格：如果用于查找值的单元格在其值的前面和/或后面包含额外的空格，则 VLOOKUP 公式会返回 #N/A 错误。

如果我们的工作表中有许多包含额外空格的单元格，我们可以使用 TRIM 函数并将查找值包含在其中，以通过 VLOOKUP 获取所需的结果。
查找列中的额外空格：如果其中一个单元格或整个查找列在其值的前面和/或后面包含额外的空格，则 VLOOKUP 公式会返回 #N/A 错误。

在这种情况下，修复 VLOOKUP 中的错误并不容易。我们必须编辑每个相应的单元格并删除空格。但是，如果查找列中有太多的单元格包含空格，最好使用 INDEX、MATCH 和 TRIM 函数的组合作为数组公式，如下所示：

输入整个公式后，必须按“Ctrl + Shift + Enter”而不是仅按 Enter 键，这一点很重要。这是必需的，因为我们正在使用数组公式。

VLOOKUP 中的 #NAME? 错误

#NAME? 错误是最常见且易于修复的 VLOOKUP 错误之一。当我们在函数名称中不小心输入错误或拼写错误时，通常会出现此错误。例如，我们可能输入 VLOKUP 或 CLOOKUP 而不是 VLOOKUP，然后看到 #NAME? 错误而不是预期的结果。

在函数名称拼写错误的情况下，Excel 找不到该函数，并返回 #NAME? 错误类型的值。

要修复 VLOOKUP 中的 #NAME? 错误，我们必须确保输入正确的函数或公式名称。更正函数名称后，错误即可解决。

VLOOKUP 中的 #REF! 错误

VLOOKUP 中的 #REF! 错误是由于错误的引用号引起的。输入 VLOOKUP 公式时，我们必须指定用于获取结果的精确列索引号。如果我们不小心输入的列索引号大于所选范围，VLOOKUP 公式将返回 #REF! 错误。

在上表中，我们提供了正确的表范围和查找值，但输入的列索引号（为 3）超出了选择范围。由于我们选择了 A2:B9 范围，因此在表范围内只提供了两列。纠正列索引号可以修复 VLOOKUP 中的 #REF! 错误。

由于我们只有两列，并且第二列是我们需要的用于提取结果值的列，因此我们将列索引号指定为 2，而不是 3。

VLOOKUP 中的 #VALUE! 错误

通常，VLOOKUP 中的 #VALUE! 错误会在公式中提供的值数据类型错误时发生。除此之外，还有一些其他原因，如下所示：

1. 查找值包含超过 255 个字符

不幸的是，Excel 的 VLOOKUP 公式无法查找超过 255 个字符的值。如果查找值中有超过 255 个字符，公式将返回 #VALUE! 错误。

要在此类情况下修复 #VALUE! 错误，我们必须使用 INDEX 和 MATCH 函数的组合，如下所示：

输入完整的 INDEX 和 MATCH 组合公式后，必须确保按“Ctrl + Shift + Enter”而不是仅按 Enter 键。

2. 缺少参数

如果 VLOOKUP 公式应用的参数有缺失，可能会导致 #VALUE! 错误。VLOOKUP 公式必须先提供查找值，然后是表范围，接着是列索引号和匹配类型。

在下面的工作表中，由于应用的公式中缺少查找值，因此出现了 #VALUE! 错误。

要修复 VLOOKUP 中的 #VALUE! 错误，我们必须提供所有必需的参数并确保它们的顺序正确。

3. col_index_num 小于 1

如果我们不小心输入的列索引号小于 1，VLOOKUP 公式将返回 #VALUE! 错误。

在上表中，我们提供了正确的表范围和查找值，但输入的列索引号（为 0）小于 1。用户有意输入小于 1 的列号是不寻常的，因为我们使用 VLOOKUP 从特定列获取结果。这意味着工作表中至少必须有一列。但是，有时可能会出现数字小于 1 的情况，特别是当此参数由嵌套在我们 VLOOKUP 公式中的其他函数返回时。

纠正列索引号可以修复 VLOOKUP 中的 #VALUE! 错误。

避免 VLOOKUP 错误

Excel 的 VLOOKUP 函数与其他 Excel 函数相比，有许多局限性。由于这些限制，我们必须始终使用“插入函数”向导来使用此函数。使用向导，我们可以获取有关所需参数的信息，这有助于消除错误。此外，我们可以遵循以下预防措施来避免或防止我们的工作表出现 VLOOKUP 错误：

数据范围包含重复值：当我们的数据范围中有重复值时，VLOOKUP 函数无法提供精确的结果。该函数仅显示它找到的第一个值的结果，而保留所有其他重复值不变。因此，VLOOKUP 不能用于包含重复值的范围。
尝试从左侧提取数据：请务必注意，VLOOKUP 函数无法查找其左侧的数据。当我们应用 VLOOKUP 并输入或提供数据范围时，我们必须确保搜索列是我们定义的范围的最左边的列。
VLOOKUP 区分大小写：Excel 的 VLOOKUP 函数不区分大小写。这意味着该函数将大写字母和小写字母视为相同的字符。如果我们的工作表同时包含大写和小写字母的条目，VLOOKUP 函数将相应地对两种情况都有效。
在表中插入或删除列：插入 VLOOKUP 公式后，我们必须避免更改数据集。如果我们进行添加或删除列等更改，VLOOKUP 函数可能会导致意外错误。当我们添加或删除列时，诸如 col_index_num 和 table_array 等参数会在数据集中以及 VLOOKUP 函数中发生更改。这会进一步导致 VLOOKUP 错误。
复制带相对引用的公式：我们必须避免复制 VLOOKUP 公式，尤其是在我们有相对引用的情况下。如果我们确实需要复制公式，可以使用绝对单元格引用。我们需要在 table_array 中包含 **$** 符号以使单元格成为绝对引用。我们可以立即按 **F4** 功能键使单元格成为绝对引用。通过使单元格成为绝对引用，我们可以牢固地锁定它们的引用，将它们复制到另一个单元格将不再造成问题。

下一主题Excel 自动化

Excel VLOOKUP 错误