Excel 中的 8 个 VLOOKUP 限制以及最佳解决方案

Excel 中最常用的功能之一是 VLOOKUP 函数。它允许用户在表中查找数据并返回相应的值。然而，虽然 VLOOKUP 是一个强大的工具，但它也有其局限性。在本文中，我将探讨 VLOOKUP 的 8 个限制，并提供一些最佳解决方案来帮助您克服这些限制并充分利用 Excel 电子表格。

第一个限制：VLOOKUP 只能向右搜索，不能向左搜索

VLOOKUP 函数的主要缺点之一是它只能在表数组中向右查找。如果要提取的数据位于查找值的左侧，则此函数将返回 #N/A 错误。

在下面的场景中，我尝试查找员工 ID 105 的工资。

公式

=VLOOKUP(A2,A5:D12,4,FALSE)

VLOOKUP 函数完美运行并返回了相应的工资，$45,000。在本例中，我想要提取的数据（薪水）位于查找值（ID）的右侧。

现在，我们尝试使用VLOOKUP函数来查找员工姓名对应的员工ID。

公式

=VLOOKUP(A2,A5:D12,1,FALSE)

这次 VLOOKUP 函数提取数据失败，但返回 #N/A 错误。在本例中，我想要查找的数据 (ID) 位于查找值 (Name) 的左侧。

克服VLOOKUP从左到右查找的局限性

要同时查看右侧和左侧，您可以使用 INDEX 和 MATCH 函数而不是 VLOOKUP 函数。

在以下场景中，我尝试提取员工 ID，并提供员工姓名作为查找值。此处，ID 列位于名称列的左侧。

句法

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])

公式

=INDEX(A5:D12,MATCH(A2,B5:B12,0),1)

配方分解

MATCH(A2,B5:B12,0)：此处，MATCH 函数在 B5:B12 范围内查找 James Kim (A2) 的精确匹配项（用 0 表示）。然后，它返回匹配的行号，即 4。

因此，公式变为 INDEX(A5:D12,4,1)。现在，INDEX 函数在表数组 A5:D12 中查找第四列和第一列的交集单元格。交叉单元格是 A8，其中包含 id 号 104。

因此，上式的输出为104。

第二个限制：VLOOKUP 不能查找多于一列

比方说，我们想要检索 Bob 从 West 订购的数量。这里，我们有 2 位名叫 Bob 的客户。第一个鲍勃来自北方，第二个鲍勃来自西方。

由于 VLOOKUP 函数无法基于多个条件工作，因此它只会检索第一个查找值（来自 North 的 Bob）的数据。这就是为什么输出是 458。

克服VLOOKUP函数无法查找多列的问题

由于VLOOKUP函数无法处理多个条件，因此我们需要将多列合并为一列。组合列是辅助列。

在本例中，我使用以下公式将“客户”和“区域”列合并到“帮助程序列”中：

公式

=CONCAT(A5,"-",B5)

在这里，我使用 CONCAT 函数将客户名称与其区域名称合并，并用破折号 (-) 分隔。

现在，我使用客户-区域组合数据作为以下 VLOOKUP 公式中的查找值：

公式

=VLOOKUP(A2,C5:E12,3,FALSE)

这次VLOOKUP函数就可以正常工作了。并且它已检索到客户 Bob 从 West 订购的数量。所以，输出是 442。

请记住，这里的表数组是 C5:E12。所以，它是从辅助栏开始的。由于 VLOOKUP 函数从表数组的最左边的列搜索查找值，因此范围是从 C 列而不是 A 列开始的。

第三个限制：VLOOKUP 仅限于表数组的最左列

VLOOKUP 函数的另一个重要限制是它只能在表数组的最左列中搜索查找值。否则，它将返回 #N/A 错误。

在以下数据集中，查找值 (Sarah Lee) 位于表数组 A5:D12 的第二列中。由于查找值不是表数组的最左列，因此它返回 #N/A 错误。

VLOOKUP 仅限于表数组最左列的 3 种解决方案

第一种方法

如果查找值不在最左列中，则需要移动表中的列以确保查找值位于表数组的最左列中。

第二种方法

您可以更改表数组，使包含查找值的列成为表数组的第一列。

第三种方法

最后，您可以使用 INDEX-MATCH 公式来执行搜索。使用此公式，您可以足够灵活地选择查找值和表数组。

公式

=INDEX(F5:I12,MATCH(F2,G5:G12,0),1)

第四个限制：VLOOKUP 不区分大小写

VLOOKUP 的主要限制之一是它无法执行区分大小写的搜索。看下图，查找值是正确的大小写（Korba）。但查找公式返回的是 KOBRA 的工资，且全部大写。

在这里，Korba 和 KOBRA 并不相同。

克服VLOOKUP区分大小写的限制

要使 VLOOKUP 函数区分大小写，必须使用 CHOOSE & EXACT 函数。

句法

=VLOOKUP(TRUE, CHOOSE(, EXACT(lookup_value, lookup_array), return_array), 2, 0)

公式

=VLOOKUP(TRUE, CHOOSE(,EXACT(A2, A5:A12), D5:D12), 2, FALSE)

配方分解

VLOOKUP 函数的第一个参数是“TRUE”，这意味着该函数将搜索 A2 中的查找值与表数组第一列中的值之间的精确匹配。

VLOOKUP 函数的第二个参数是嵌套的 CHOOSE 函数。 CHOOSE 函数用于创建两列的数组。

=选择(,精确(A2,A5:A12),D5:D12)

=

第一列包含 EXACT 函数的结果，如果 A2 中的值与范围 A5:A12 中的任何值匹配，则返回 TRUE，否则返回 FALSE。

=精确（A2，A5：A12）

=

第二列包含 D5:D12 范围内的值，这些值对应于第一列中的每一行。

VLOOKUP函数的第三个参数是“2”，这表示该函数将返回表数组第二列（即D列）中的值。

VLOOKUP函数的第四个参数是“FALSE”，这意味着该函数只会返回精确匹配，而不返回近似匹配。

总体而言，此公式正在搜索单元格 A2 中的值与 A5:A12 中的一系列值之间的精确匹配，然后返回找到匹配的行的 D 列中的相应值。

第 5 个限制：大数据集性能低下

处理大型数据集时，VLOOKUP 可能会很慢，尤其是在同一工作簿中多次使用时。这是因为 VLOOKUP 必须扫描表的每一行才能找到匹配的值，这可能非常耗时。通常，查找一个范围大约需要 6.6 秒，查找整个列大约需要 14.2 秒。

为了提高性能，用户可以使用更快、更高效的替代函数，例如 INDEX-MATCH 或 XLOOKUP。

第 6 个限制：每次查找仅限一个值

即使表中有多个匹配值，VLOOKUP 每次查找也只能返回一个值。当处理重复值或用户想要从表中返回多个值时，这可能是一个问题。

解决方案#1：使用公式查找并返回多个值

为了克服此限制，您可以使用以下公式的 TEXTJOIN、IF、IFERROR、MATCH 和 ROW 函数。此公式可以返回单个单元格内的多个值，并用逗号 (,) 分隔。它还会自动消除所有重复值并仅返回值的第一个实例。

句法

=TEXTJOIN(", ", TRUE, IF(IFERROR(MATCH(return_array, IF(lookup_value=lookup_array,return_array, ""), 0),"")=MATCH(ROW(return_array),ROW(return_array)),return_array, ""))

公式

=TEXTJOIN(", ", TRUE, IF(IFERROR(MATCH(C5:C12, IF(A2=A5:A12,C5:C12, ""), 0),"")=MATCH(ROW(C5:C12),ROW(C5:C12)),C5:C12, ""))

解决方案 #2：使用 VBA 查找并返回多个值

如果您不想使用复杂的公式通过垂直查找返回多个值，可以考虑以下VBA代码。这段代码创建了一个名为 LookupMultipleValues 的用户定义函数。该函数只需要3个参数，使用起来很方便。

Function LookupMultipleValues(gTarget As String, gSearchRange As Range, gColumnNumber As Integer) Dim g As Long Dim k As String For g = 1 To gSearchRange.Columns(1).Cells.Count If gSearchRange.Cells(g, 1) = gTarget Then For J = 1 To g - 1 If gSearchRange.Cells(J, 1) = gTarget Then If gSearchRange.Cells(J, gColumnNumber) = gSearchRange.Cells(g, gColumnNumber) Then GoTo Skip End If End If Next J k = k & " " & gSearchRange.Cells(g, gColumnNumber) & "," Skip: End If Next g LookupMultipleValues = Left(k, Len(k) - 1) End Function

Visual Basic复制

使用指南

步骤_1：按 ALT + F11 打开 Visual Basic 编辑器。

步骤_2：转到“插入”>“模块”。

Step_3：粘贴上面的代码。

步骤_4：再次返回工作表并使用以下公式。

句法

=LookupMultipleValues(lookup_value,lookup_array,col_index_num)

公式

=LookupMultipleValues(A2,A5:A12,3)

第七个限制：VLOOKUP 函数在添加或删除列方面不灵活

当您在数据区域中间插入或删除列时，可能会导致 VLOOKUP 公式出现问题。因为您的 VLOOKUP 引用的列索引号可能不再准确。

通常，VLOOKUP 函数的第三个参数（即列索引号）是硬编码的。这就是为什么在表数组中添加或删除列可能会导致结果不准确的原因。

解决 VLOOKUP 关于添加或删除列的灵活性问题

为了解决这个问题，可以使用COLUMN函数来计算列索引号，而不是对其进行硬编码。对于以下场景，这就是使用和不使用 COLUMN 函数时公式的外观。

静态列索引号

=VLOOKUP(A2,A5:D12,3,FALSE)

动态列索引号

=VLOOKUP(A2,A5:D12,COLUMN(),FALSE)

要了解更多信息，您可以阅读 Excel 中 VLOOKUP 计算列索引号的 2 种方法。

第 8 个限制：默认情况下，VLOOKUP 返回近似匹配结果

让我们记住 VLOOKUP 函数的语法。

句法

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

您可以看到 VLOOKUP 函数的第四个参数是可选的。但 range_lookup 参数的默认设置是 TRUE – 近似匹配，这是一个问题。因为，如果不指定 range_lookup 参数，它将执行近似匹配。因此，您可能不会得到您想要的结果。

如果您的数据未排序，并且忽略 range_lookup 参数或将其设置为 TRUE-近似匹配，则 VLOOKUP 函数将不会返回准确的结果。

因此，如果您正在查找lookup_value和table_array之间的精确匹配，请确保将range_lookup参数指定为FALSE – 通过输入FALSE或0来精确匹配。

Excel中各种查找函数的比较

功能 优点 缺点 索引/匹配可以执行从左到右的查找。 更长的公式语法。 X查找支持近似匹配和精确匹配。需要较新版本的 Excel。 查询可以执行水平查找。仅限一排。 错误/查找可以处理错误并返回替代结果。仅限于垂直查找。 选择/匹配可以基于数字索引执行查找。仅限于垂直查找，不如 INDEX/MATCH 公式灵活。

结论

虽然 VLOOKUP 是 Excel 中用于数据分析的强大工具，但它也有其局限性。通过使用我们在本文中概述的最佳解决方案，您可以克服这些限制并将数据分析提升到新的水平。无论您选择使用 INDEX-MATCH、IFERROR、通配符、串联还是辅助列，都有多种方法可以充分利用 Excel 电子表格并获得您需要的结果。通过一些练习和实验，您将能够掌握这些技术并立即成为一名 Excel 专家。