XLOOKUP() 与 VLOOKUP()：面向 Excel 用户的比较

XLOOKUP() 与 VLOOKUP() 的语法

在了解了两者的基本概念后，我们来看看它们的语法以及在实际应用中的用法。 

VLOOKUP() 的语法

先来看 VLOOKUP() 的语法。 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])技术支持

上述公式中：

• lookup_value 是您要查找的值。可以是数字、文本，或包含查找值的单元格引用。

• table_array 是包含数据的单元格区域。该区域的第一列应包含 search_key。

• col_index_num 是要从中提取值的列在该区域内的列号。 

• range_lookup 用于输入逻辑值（TRUE 或 FALSE）。

XLOOKUP() 的语法

接下来看看 xlookup() 的语法。可以看到，XLOOKUP() 的参数比 VLOOKUP() 更多。

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])技术支持

上述公式中：

• lookup_value 是您要查找的值。

• lookup_array 是进行查找的单元格区域。

• return_array 是包含要返回值的单元格区域。

• if_not_found（可选）在未找到匹配时返回的值。若未指定，将返回 #N/A。

• match_mode（可选）决定匹配类型。0 为精确匹配（默认）；-1 为精确匹配或下一个较小项；1 为精确匹配或下一个较大项；2 为通配符匹配。

• search_mode（可选）决定搜索模式。1 为从首到尾搜索（默认）；-1 为从尾到首搜索；2 为按升序进行二分搜索；-2 为按降序进行二分搜索。

主要差异：XLOOKUP() 与 VLOOKUP()

下面来看看 XLOOKUP() 与 VLOOKUP() 的一些关键差异。

精确匹配模式 

XLOOKUP() 默认使用精确匹配，而 VLOOKUP() 需要将参数指定为 FALSE 才能进行精确匹配。

XLOOKUP() 与 VLOOKUP() 在精确匹配上的差异。来源：作者制图。

在 VLOOKUP() 中，我未指定 range_lookup 值（第 4 个参数），因此它返回了最接近的匹配，这并非我们想要的结果。而 XLOOKUP() 则默认返回精确匹配。

查找方向

VLOOKUP() 只能在所选表格区域第一列的右侧进行查找；而 XLOOKUP() 可以在任意方向查找。

VLOOKUP() 与 XLOOKUP() 在查找方向上的差异。来源：作者制图。

此处，VLOOKUP() 未能找到成绩，因为它只能在 lookup_value（学生姓名）右侧查找。相比之下，XLOOKUP() 能在任一方向（左或右）查找，因此找到了 Robin 的成绩。

数组引用

在 VLOOKUP() 中，您需要在单一公式里定义整个数据范围（表数组）并指定包含所需输出（返回列）的列号。XLOOKUP() 更为灵活，允许分别定义查找值的数组和要返回的数据数组。 

VLOOKUP() 与 XLOOKUP() 的数组引用差异。来源：作者制图。

水平查找

VLOOKUP() 无法执行水平查找；若需要水平查找，必须使用 HLOOKUP()。XLOOKUP() 将这两种能力合二为一，既可垂直查找也可水平查找，无需再为不同方向使用不同函数。

VLOOKUP() 与 XLOOKUP() 在水平查找上的差异。来源：作者制图。

列插入/删除的处理 

由于 column_index_num 是硬编码的，列位置变化会影响 VLOOKUP()。而 XLOOKUP() 不受列变动影响，公式无需调整即可继续工作。

XLOOKUP() 不受影响，而 VLOOKUP() 失败。来源：作者制图。

排序与搜索

您可以将 VLOOKUP() 设为升序排序，但在处理未排序数据时会较为困难。而 XLOOKUP() 可通过 search_mode 参数在升序与降序间进行搜索。

在 XLOOKUP() 与 VLOOKUP() 中处理插入/删除。来源：作者制图。

自定义错误信息

VLOOKUP() 在未找到匹配时会显示 #N/A 错误。XLOOKUP() 具有可选的 if_not_found 参数，可在未找到值时自定义输出文本。 

在 XLOOKUP() 与 VLOOKUP() 中自定义错误信息。来源：作者制图。

可以看到，VLOOKUP() 未能在列表中找到该学生并显示了 #N/A 错误。同样，XLOOKUP() 也未找到该学生姓名。但与 VLOOKUP() 不同，XLOOKUP() 会在无匹配时返回特定消息。

返回多个值

VLOOKUP() 每次只能返回一个值，而 XLOOKUP() 可同时从多列提取值。因此，您可以用一个 XLOOKUP() 公式替代多个 VLOOKUP() 公式。

使用 XLOOKUP() 与 VLOOKUP() 返回多个值。来源：作者制图。

搜索模式

VLOOKUP() 会从列表开头往下查找，仅返回第一个匹配值。而 XLOOKUP() 可按任一方向扫描列表（自上而下或自下而上），在大列表中也能快速定位。

VLOOKUP() 与 XLOOKUP() 的差异。来源：作者制图。

此处，VLOOKUP() 返回了第一次出现的值，即第一学期的数据。而使用 XLOOKUP() 时，我通过搜索模式代码 -1 自下而上进行查找。

实践示例与用例

下面来看一些实用示例与用例。

无需重排表格即可查找

我有一份学生姓名与成绩的列表，想根据姓名查找每位学生的成绩。为此，我将分别使用 XLOOKUP() 与 VLOOKUP() 来展示差异。 

使用 XLOOKUP() 时，我输入以下公式：

=XLOOKUP(D8,B2:B5,A2:A5)技术支持

在该公式中：

• D8 包含查找值，即 Charlie。

• B2:B5 是学生姓名的范围。XLOOKUP() 会在此范围内查找 D8 的值。

• A2:A5 是与学生姓名对应的成绩范围。 

使用 VLOOKUP() 时，我输入以下公式：

=VLOOKUP(D4,A1:B5,1,0)技术支持

在该公式中：

• D4 包含查找值，即 Charlie。

• A1:B5 在所有列（Scores、Student Name）中进行查找。

• 1 表示返回该范围第 1 列（Scores）中的值。

• 0 表示精确匹配。

VLOOKUP() 返回 #N/A 错误，而 XLOOKUP() 可处理。来源：作者制图。

如您所见，VLOOKUP() 显示了 #N/A 错误，因为它无法向左查找。

当未找到值时创建自定义输出

我有一份包含 学号、姓名和 成绩的学生名单。查询时我不小心输入了列表中不存在的学号。为此，我希望在未找到学号时显示一条自定义消息。 我将分别使用 XLOOKUP() 与 VLOOKUP() 来展示差异。 

使用 XLOOKUP() 时，我输入以下公式：

(=XLOOKUP(E9, A2:A5, B2:B5, "Student not Found")) 技术支持

在该公式中：

• E9 包含我们要查找的值。在我的示例中是 14256。

• A2:A5 是包含查找值（Student ID）的单元格范围。

• B2:B5 用于查找 ID 14526 的分数。

• Student not Found 会在列表中没有该分数时显示。 

使用 VLOOKUP() 时，我输入以下公式：

(=VLOOKUP(E6, A2:C5,2,0)) 技术支持

在该公式中：

• E6 指代 Student ID，也就是我要查找的内容。

• A2:C5 指代包含表格全部数据的范围。

• 2 表示要返回的数据所在的列号。

• 0 表示精确匹配。

XLOOKUP() 会返回提示信息，而 VLOOKUP() 不会。来源：作者制图。

您可以看到 14256 不在 A2:A5 范围内，因此 XLOOKUP() 返回了自定义消息 Student not found。而 VLOOKUP() 则抛出 #N/A 错误。 

自下而上搜索

我想找到 Sarah 最近一年的薪资。为此，我将分别使用 XLOOKUP() 与  VLOOKUP() 来展示差异。 

使用 XLOOKUP() 时，我输入以下公式：

=XLOOKUP(F8,B2:B10,C2:C10,,,-1)技术支持

在该公式中：

• F8 包含查找值 Sarah。

• B2:B10 在该范围内搜索 Sarah。

• C2:C10 用于返回数据。

• -1 表示从列底部开始搜索。 

使用 VLOOKUP() 时，我输入以下公式：

=VLOOKUP(F4, B2:C10,2,0)技术支持

在该公式中：

• F4 包含查找值 Sarah。

• B2:C10 在此范围内查找匹配值。

• 2 表示返回的列号为 Salary。

• 0 表示精确匹配。

搜索模式下 VLOOKUP() 与 XLOOKUP() 的差异。来源：作者制图。

可以看到，XLOOKUP() 找到了目标年份的目标薪资，而 VLOOKUP() 只显示了第一个匹配值。我之所以能实现，是因为 XLOOKUP() 支持自下而上的搜索。

性能与兼容性

接下来对比两者在性能与兼容性方面的表现。 

大数据集中的性能

VLOOKUP() 在处理大型数据集时可能较慢，尤其当查找列未排序且将范围查找参数设置为 FALSE（精确匹配）时。原因在于 VLOOKUP() 会按顺序扫描数据集直至找到匹配，这对大型表来说较耗时。XLOOKUP() 在大型数据集上表现更佳，可更高效地处理精确匹配，并能在不排序的情况下横向与纵向搜索。 

与不同 Excel 版本的兼容性

看到 XLOOKUP() 的优势后，您也许想完全放弃 VLOOKUP()。但需要说明的是——尽管 XLOOKUP() 更快且功能出色，它也有不可用的时候。这是因为 XLOOKUP() 是较新的功能，仅适用于 Excel 2021 和 Microsoft 365（自 2019 年起）。如果您与使用更早版本 Excel 的人协作，就需要使用 VLOOKUP() 等函数来替代 XLOOKUP()。

总结表

我们来制作一张总结表，便于参考。 

结语

XLOOKUP() 尤其在处理大型数据集或复杂查找需求时，整体表现优于 VLOOKUP()。其灵活性（包括双向搜索、返回多结果、适应数据变化）使之成为非常实用的工具。不过，VLOOKUP() 仍有其用武之地，特别适用于旧版 Excel 用户或偏好用更简单方式完成基础查找的人。 最终选择取决于您的具体需求与 Excel 版本。

如果您是 Excel 新手或想夯实基础，我们的 Introduction to Excel 课程以及 Excel Fundamentals 技能路径都是入门基础的理想选择。