为什么每个 Excel 用户今天都应该用 XLOOKUP 替代 VLOOKUP

电子表格公式曾经觉得很脆弱。一个错误的列号就可能搞砸整份报告。但当我终于用 XLOOKUP 替代 VLOOKUP 后，Excel 开始感觉可预测、灵活，并且出乎意料地难以损坏。

解析最终让 Excel 逻辑明白的语法

现代查找公式的构造

在我深入讲解为何放弃旧的工作流程之前，先看看这些函数如何与 Excel 进行通信会有帮助。VLOOKUP 成为常用的查找公式，是因为数据传统上是纵向堆叠在列中，而不是横向分布在行中。

它需要四个严格的参数。你必须给它一个查找值，指向整个数据块，告诉它要数到哪一列，然后明确输入 FALSE，这样它就不会给你一个随机的近似匹配：

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

XLOOKUP用更灵活的方法取代了这种不灵活的设置。你只需指定要搜索的值、要搜索的列以及要返回的数据，而不是引用大型表格范围并计算列数：

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

如果将数据集转换为 Excel 表格（按下 Ctrl+T 或点击 插入 > 表格），XLOOKUP 会变得更易读，因为它们将基于位置的引用转换为命名的关系。在下面的所有示例中，我将使用一个名为 StaffDirectory 的表格，其中包含五个不同的列：ID、姓名、部门、角色和电子邮件。

VLOOKUP 让我像人工计算器一样数列

当列顺序打乱一切时

让我总是感到沮丧的是 VLOOKUP 的手动列计数。如果你想根据单元格 A2 中的名字提取 David Cho 的电子邮件地址，整表查找不会返回你期望的结果，因为 VLOOKUP 只能搜索你提供的范围的第一列。由于该列是“ID”，而不是“Name”，VLOOKUP 找不到你要查找的值，因此公式会返回错误：

=VLOOKUP($A$2, StaffDirectory, 5, FALSE)

为了强制 VLOOKUP 工作，你必须将公式范围扭转到从第二列开始，这会立即让你的计算看起来很混乱：

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 4, FALSE)

注意到那里发生了什么吗？因为我必须修改表格数组以从“Name”列开始，所以列索引号从5变为了4。然而，如果你忘记你没有引用整个表格而仍然写成5，你将得到一个 #N/A 错误。如果你数错并输入3，公式会返回他的职位，而不是他的电子邮件。

更糟的是，即使你的公式现在是正确的，如果有人在该特定子集中的任意位置插入新列，你硬编码的索引号就会立即失效，从而导致报告出错。

但是当我们使用 XLOOKUP 时，原始数字、自定义范围和列计数都会消失。公式读起来就像一句口语化的句子：

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Email])

现在，即使有人插入新列或重新排列布局，公式也能保持稳定，因为它引用的是命名列而不是位置。

本地错误处理避免了使用 IFERROR 的需求

在不增加额外层的情况下处理缺失数据

与 VLOOKUP 不同，如果搜索项缺失它会停止并返回错误代码，XLOOKUP 允许你在公式内部直接处理错误。

要使用 VLOOKUP 隐藏那些丑陋的 #N/A 错误，你必须将公式包裹在 IFERROR 函数中，这会在单元格中增加不必要的干扰，并使复杂的表格更难排查问题：

=IFERROR(VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 2, FALSE),"Employee not found")

XLOOKUP 完全消除了额外的包装器，通过使用它的第四个参数，如果名单中没有某个名字，就直接在单元格中显示可读的消息：

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Department], "Employee not found")

是的，这些公式的长度可能相似，但请花点时间去理解和欣赏使用 XLOOKUP 版本构建和理解要容易得多。

精确匹配现在是默认选项，而不是隐藏陷阱

省略 VLOOKUP 的 FALSE 参数

VLOOKUP 函数在一个危险的默认设置下运行：如果你省略最后一个参数，系统会假设你想要一个近似匹配。这种默认行为为日常电子表格工作带来了两个主要问题。

首先，你可能会得到一个假阳性。假设你忘记了那个尾随参数：

=VLOOKUP($A$2, StaffDirectory[[ID]:[Name]], 2)

即使“1065”不在列表中，VLOOKUP 也不会给你错误。相反，它会根据排序顺序找到小于或等于你的查找值的最大值——在这种情况下，它会落在 Priya Patel 的 ID“1064”上并返回她的名字。结果，你的表格中会出现错误数据，并且没有任何提示说明出了问题。

其次，你可能会忘记对数据进行排序。VLOOKUP 的近似匹配引擎严格要求你的查找列按升序排列（从小到大）。如果你的表格恰好是按降序排列的——或者完全是随机的——省略最后那个参数会导致引擎返回不可预测、混乱的结果。如果数据没有正确排序，VLOOKUP 可能返回错误的匹配结果，或者有时甚至会完全失败并出现 #N/A 错误。

然而，XLOOKUP 通过将精确匹配（我之前提到的两个可选行为中的第一个）设为内置默认，消除了这两个陷阱。如果你将匹配模式参数留空，它仍会寻找精确匹配，完全忽略你的数据排序方式：

=XLOOKUP($A$2, StaffDirectory[ID], StaffDirectory[Name], "ID not found")

因为精确匹配是默认设置，所以在这里搜索“1065”可以安全地返回您的自定义“未找到ID”消息，从而保护您的电子表格免受静默假阳性和排序陷阱的影响。

改变搜索方向不应该需要复杂的变通方法

从底部向上扫描而不对数据进行排序

随着数据集的增长，你通常需要在运行日志中查找最新的信息，而不是最旧的信息。这是我之前提到的两个有用的可选行为中的第二个：反向搜索方向。

看看我们的示例数据集：名字“Marcus Vance”出现了两次，因为他从“市场部”（数据行 1）调到了“销售部”（数据行 5）。换句话说，数据行 5 包含了他最新的信息。

然而，除非你对数据进行排序或编写密集的数组数学，否则标准的 VLOOKUP 将返回它遇到的第一个匹配项（在此例中为数据行 1），而会错过更新的条目：

=VLOOKUP($A$2, StaffDirectory[[Name]:[Department]], 2, FALSE)

使用 XLOOKUP，更改搜索方向只意味着在第六个参数中输入 -1：

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[Department], "Employee not found", 0, -1)

这会进行自下而上的扫描，所以它会返回他当前的部门。

返回整个数组可以防止公式以后出错

使用单个公式提取多行多列

如果您想使用 VLOOKUP 将员工的部门、角色和电子邮件拉入汇总行，您可能会编写三个单独的查找公式，并在三个单元格中手动更改索引号：

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 2, FALSE)

和

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 3, FALSE)

和

=VLOOKUP($A$2, StaffDirectory[[Name]:[Email]], 4, FALSE)

动态数组通过溢出使这一过程更简单。通过为返回数组选择一块相邻的列，一个 XLOOKUP 公式即可自动填充多个单元格：

=XLOOKUP($A$2, StaffDirectory[Name], StaffDirectory[[Department]:[Email]])

这使你需要维护的公式大大减少，从而更容易处理大型工作表。

拥抱更智能的 Excel 搜索方式

用 XLOOKUP 替换 VLOOKUP 完全改变了我构建电子表格的方式。我不再需要担心损坏的列索引、隐藏的近似匹配或僵硬的表格布局，我可以花更少的时间修复公式，而有更多时间真正处理数据。当然，这个函数并不是唯一的老朋友的现代版本。如果你习惯使用 INDEX-MATCH 组合来处理复杂的网格，改用 XMATCH 替代传统的 MATCH 函数，意味着你可以将相同的安全、精确匹配默认设置应用到行和列的搜索中。