XLOOKUP()：Excel 中最通用的查找函数

如果您曾使用过 VLOOKUP()，却因需要向左搜索而卡住，XLOOKUP() 就能解决这个问题。它是 Excel 较新的查找函数。它可以用一个简单的公式替代 VLOOKUP()、HLOOKUP()，甚至大多数 INDEX() 与 MATCH() 的组合。

XLOOKUP() 可以按任意方向搜索，一次返回多个列，并且在值缺失时显示自定义消息。

在本指南中，我们将先讲解语法。随后用一套简单的数据集，循序渐进构建真实的公式。我们还将介绍精确与近似匹配、如何返回多列、如何处理错误，以及 XLOOKUP() 与 VLOOKUP()、INDEX()/MATCH() 的比较。

如何在 Excel 中使用 XLOOKUP()

让我们从零开始构建一个真实的 XLOOKUP() 公式。

我们的数据集包含五个产品，每个都有 ID、名称、类别和价格，位于 A 到 D 列，第 1 行是表头。

XLOOKUP() 的数据集。图片来自作者。

比如我们想查找 “P104” 的价格。我们一步步来。

查找单个值

单击一个空白单元格作为结果显示位置。我用 F2。然后输入以下公式：

=XLOOKUP("P104", A2:A6, D2:D6)

在这个公式中：

• “P104” 是 lookup_value，即我们要查找的值

• A2:A6 是 lookup_array，即 Excel 搜索的范围

• D2:D6 是 return_array，即 Excel 返回答案的范围

Excel 在 A 列（“Product ID”）中搜索 “P104”，在第 5 行找到，并返回 D 列中对应的值：$45。

您也可以不直接输入 “P104”，而是引用单元格，这样可以在不编辑公式的情况下更改查找值。在我们的例子中，单元格 A5 包含 “P104”，因此公式变为：

=XLOOKUP(A5, A2:A6, D2:D6)

在 Excel 中使用 XLOOKUP()。图片来自作者。

现在您可以在 A5 中输入任意产品 ID，价格将自动更新。

从另一列返回值

这一步展示了 XLOOKUP() 与 VLOOKUP() 的真正差别。您不必只能从搜索列右侧的某一列返回值。

假设您想要的不是价格，而是 “P102” 的类别。设置相同，只需将 return_array 改为 C 列（“Category”）：

=XLOOKUP(A3, A2:A6, C2:C6)

这里，A3 返回 Home。如果改为 A5，则更新为 Electronics。类似地，再次更改 return_array 即可提取产品名称、价格或其他任意列，且都使用相同的 lookup_value 和 lookup_array。

XLOOKUP() 从另一列返回值。图片来自作者。

使用 VLOOKUP() 时，您需要从搜索列开始数列数，并在每次想要不同结果时调整列索引号。用 XLOOKUP()，您只需直接指向所需的列。

将公式复制到多行

目前为止，我们一次只查找一个值。但大多数真实表格通常需要同时查找很多值，比如一整列订单 ID 都需要匹配价格。

设置方法如下：A 列从 A2 开始已经包含了我们的产品 ID 列表。我们希望在每个 ID 右侧的 F 列显示对应的价格。

为此，单击 F2，输入公式，并像下面这样引用 A2 作为查找值：

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

然后再次单击 F2，拖动填充柄（单元格右下角的小方块）向下覆盖其余各行。

Excel 会向下复制公式，每一行都会返回与其自身产品 ID 匹配的正确价格。A2 的 ID 得到 F2 的价格，A3 的 ID 得到 F3 的价格，依此类推。

将公式向下复制到其他单元格。图片来自作者。

注意 A2:A6 和 D2:D6 周围的美元符号。这称为绝对引用。它会将范围固定住，这样在向下复制公式时就不会发生偏移。

这在此处很重要，因为每一行都需要在相同的产品列表中搜索，只是每次的查找值不同。

如何在 XLOOKUP() 中使用精确匹配与近似匹配

默认情况下，XLOOKUP() 会查找精确匹配。如果找不到，会返回错误，除非您设置了我们前面提到的 if_not_found 参数 。

但 XLOOKUP() 也可以利用 match_mode 参数找到最接近的匹配。

精确匹配（默认）

您无需额外输入任何内容。到目前为止我们构建的每个公式都已经使用了精确匹配，因为这是 XLOOKUP() 的默认行为。

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel 会查找 A2 的精确值，只有在查找范围中找到完全一致的匹配时才返回结果。

如果您想显式写出这一点，可将第四个参数设为 0：

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() 返回精确匹配。图片来自作者。

注意 0 前面的空位？那是跳过 if_not_found（此处留空）直接进入 match_mode。多数情况下，您可以完全省略这个参数，让 Excel 默认执行精确匹配。

近似匹配

现在，假设您的数据并没有为每个可能的查找值都提供一行。想想提成结构：销售代表的提成率取决于总销售额落入哪个区间。您不会为每个金额都建一行，只会记录每个区间的起始点。

这时，近似匹配就派上用场了。

将 match_mode 设为 -1，XLOOKUP() 会找到小于或等于您的查找值的最大值。

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

假设您的查找范围包含 $0、$5,000 和 $10,000 的销售门槛，每个对应不同的提成率。

一位销售代表的销售额是 $7,500，与任何一行都不完全匹配。将 match_mode 设为 -1 时，XLOOKUP() 会找到 $5,000——不超过 $7,500 的最大门槛——并返回该行的提成率。

若将 match_mode 改为 1，XLOOKUP() 会反转逻辑：找到大于或等于您的查找值的最小值。这对诸如运费重量分档等场景很有帮助，因为此类情况是向上取整到下一档，而不是向下。

XLOOKUP() 返回近似匹配。图片来自作者。

注意：近似匹配假设您的查找范围已排序，-1 为升序、1 为降序。如果排序不正确，XLOOKUP() 可能在不报错的情况下返回错误结果，这更糟。因此，在依赖近似匹配前，请务必再次确认排序。

如何用 XLOOKUP() 向左查找值

VLOOKUP() 只能在您指定范围的最左列搜索，并从其右侧的列返回值；反向不行。其第三个参数并不是一个范围，而是从左边缘起算的列索引。

它无法理解“往左一列”，因此如果您需要的值在搜索列的左侧，就只能重排数据或添加辅助列来实现查找。

XLOOKUP() 不受此限制，因为您可以分别为搜索与返回指向不同的范围。

以我们的产品列表为例：产品 ID 在 A 列，产品名称在 B 列。

假设您有一个产品名称并想找到它的 ID；这正是 VLOOKUP() 无法直接完成的反向查找。

在 C9 输入 Bluetooth Speaker（列表中已存在的名称）。在 E2 输入：

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() 会在 B 列搜索 “Bluetooth Speaker”，在第 5 行找到，并从 A 列返回 “P104”，尽管 A 列位于 B 列的左侧。

XLOOKUP() 可以向左查找值，不同于 VLOOKUP()。图片来自作者。

如何用 XLOOKUP() 返回多列

XLOOKUP() 借助动态数组功能，可以在一个公式中一次返回多列。方法是将 return_array 指向一个区域而不是单列，XLOOKUP() 会将整行匹配结果“溢出”到所需数量的单元格中。

让我们用产品列表来试试。

在 C9 中输入一个产品 ID，例如 “P104”。在 E2 输入：

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

注意此处的 return_array 是 B2:D6，横跨三列，涵盖产品名称、类别和价格。按下回车后，XLOOKUP() 会在 A 列找到 “P104”，然后一次性返回 Bluetooth Speaker、Electronics 和 $45，分别溢出到 E9、F9 和 G9。

XLOOKUP() 用一个公式返回多列。图片来自作者。

这就是溢出区域（spill range）的含义。E9 存放实际公式，F9 和 G9 会自动填充自身，并不含有各自的公式。

单击 F9，公式栏会显示原公式的灰色版本，而不是在那里直接输入的内容。当 E9 的溢出处于活动状态时，尝试在 F9 中输入，Excel 会抛出溢出错误。

我们将在常见错误部分介绍如何解决。

如何在 XLOOKUP() 中处理缺失值

默认情况下，当找不到匹配时，XLOOKUP() 会抛出 #N/A 错误。通过 if_not_found 参数可以用我们希望显示的内容来替代默认错误。

假设在 C9 中，我输入一个产品 ID，它并不存在于我的数据中，比如 “P999”。

返回自定义消息

在 G2 中，我输入：

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

由于 “P999” 不存在于 A 列，XLOOKUP() 会跳过错误，直接返回 “Not Found”。您也可以改成任意提示语，如 “Check Product ID” 或 “No Match”。

以自定义消息替代默认错误。图片来自作者。

返回空白

有时您希望单元格保持空白不显示文本。这时可以使用一对引号，中间不放任何内容，如下：

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

当您在报告中使用该公式时，如果在一列真实数值旁边出现 “Not Found” 会显得杂乱，这一做法就很有用。

以空白单元格替代默认错误。图片来自作者。

保留为真实错误

如果完全跳过 if_not_found，XLOOKUP() 将回退到 #N/A。这并非总是坏事。如果您是为自己构建公式并希望立即发现拼写错误或缺失条目，看到 #N/A 比悄无声息的空白更有帮助。

注意：if_not_found 只处理缺失匹配。如果您的 lookup_array 与 return_array 尺寸不同，例如，XLOOKUP() 仍会抛出 #VALUE! 错误，而 if_not_found 并不能阻止这种情况。我们会在下方的常见错误部分专门讨论这一问题。

XLOOKUP() 高级功能

来看一下 XLOOKUP() 的高级功能。

自下而上搜索

将 search_mode 设为 -1，XLOOKUP() 会自下而上扫描范围，而不是自上而下。当数据存在重复值且您想要最新的一条而非第一条时，这很有用。

假设在我的数据集中有三个重复的 Customer ID。在 E2 中，我先输入默认公式，采用自上而下的方式：

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

这会从表格开头开始计算，并显示首次匹配的结果。

但如果我想自下而上搜索，就使用这个公式：

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

在这个公式中，-1 告诉 Excel 从数据集底部开始搜索。这将返回 340，因为那是自底向上找到的首个匹配值。

XLOOKUP() 自下而上扫描数据。图片来自作者。

二分搜索模式

将 search_mode 设为 2，XLOOKUP() 将不再逐行检查，而是改用二分搜索。

其工作原理如下：

• 它先查看范围中间的项。

• 若不匹配，就丢弃不可能存在目标值的一半数据，然后在剩余部分的中间继续查找。

• 如此反复，每次将数据减半，直到找到匹配。

• 将 search_mode 设为 -2，逻辑相同，只是从最后一项而非第一项开始。

在 E2 输入：

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

该公式却返回了 200，是完全不同的行。

二分搜索模式给出了错误结果。图片来自作者。

因为二分搜索仅在 lookup_array 已排序时才有效。而我们的 Customer ID 列并未排序，顺序为 C100、C101、C100、C102、C100，二分搜索会丢弃错误的一半数据，从而得到错误匹配。

现在看看当数据确实已排序时会发生什么。

将同一日志按 Customer ID 升序排序（将三个 C100 都排在前面），然后再次运行同一公式。

二分搜索模式适用于已排序数据。图片来自作者。

这次 E2 返回 120，与普通搜索一致。差别就在这里。二分搜索只会跳过它能被安全排除的数据，而这仅在数据一开始就排好序时成立。

嵌套的 XLOOKUP() 公式

当一次查找的结果需要传递给第二次查找（而不是固定范围）时，可以将一个 XLOOKUP() 嵌套在另一个里面。

假设您要找一个价格，但要从哪一列取值取决于另一次查找（比如客户的定价等级）。

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

在这个公式中，内部的 XLOOKUP() 会先根据 D9 判断应当从哪个列区域取值，然后外部的 XLOOKUP() 将该结果作为其 return_array。

嵌套的 XLOOKUP()。图片来自作者。

XLOOKUP() 与 VLOOKUP() 的对比

我们已经详细介绍了 XLOOKUP() 的工作方式。下面与 VLOOKUP() 做个对比。

有两点值得一提：

• VLOOKUP() 默认使用近似匹配。如果您忘了第四个参数，它不会报错；而是悄悄返回错误的值。

• 如果在 VLOOKUP() 的工作表中插入一列，公式会悄悄开始提取错误的值。XLOOKUP() 通过直接引用范围而非按列数计数，避免了这个问题。

这并不意味着 VLOOKUP() 已经过时。对于简单、静态的表，它仍然很好用。但对于今后要构建的内容，XLOOKUP() 更为稳妥。

XLOOKUP() 与 INDEX()、MATCH() 的对比

在 XLOOKUP() 出现之前，INDEX() 与 MATCH() 的组合是规避 VLOOKUP() 限制的标准做法。它使用两个函数而非一个。

• MATCH() 找到查找值的位置。

• INDEX() 再利用该位置从您指定的地方取回结果：向左或向右都可以。

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

将其与我们在整篇文章中使用的 XLOOKUP() 版本对比：

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() 仅适用于 Microsoft 365 和 Excel 2021 及更高版本，正如我们之前所述。如果您的工作簿需要在 Excel 2019 或 2016 中打开，或要与使用旧版本的人共享，INDEX() 与 MATCH() 依然处处可用。

还有一个较小的适用场景。

INDEX() 与 MATCH() 将“找到位置”和“返回值”分离开来，在某些复杂的嵌套公式中更有帮助。这种情况不常见，大多数读者可能用不到。

除上述两种情况外，对于新建的工作簿，XLOOKUP() 通常是更好的选择。

常见的 XLOOKUP() 错误及修复方法

以下是最常见问题及其修复方法的快速参考。

#N/A 错误

当找不到匹配时，这是 XLOOKUP() 的默认响应。我之前就遇到过这种情况，当时 lookup_value 与 lookup_array 对不齐，XLOOKUP() 在 A 列无从匹配。

修复方法：仔细检查您的 lookup_value 是否确实存在于 lookup_array 中，且拼写与格式一致。如果缺失匹配是预期且正常的情况，使用 if_not_found 参数将 #N/A 替换为更有用的内容，例如 “Not Found” 或空白，正如前文所述。

查找范围不正确

当 lookup_array 与 return_array 未正确对齐时会发生这种情况：行不对、列不对，或范围大小不一致。

取决于您使用的 Excel 或表格版本，这可能会显示为 #VALUE! 错误，有时也会显示为 #N/A，并附带另一条消息解释真正的问题。不要被 #N/A 迷惑，以为只是缺失匹配。

修复方法：确保 lookup_array 与 return_array 覆盖相同数量的行或列。如果 lookup_array 是 A2:A5，那么 return_array 也需要覆盖第 2 到第 5 行，而不是第 4 到第 5 行或其他不匹配的范围。

数据类型不匹配

VLOOKUP() 可能会因期望数字却得到文本而抛错。XLOOKUP() 也会遇到类似问题。如果您的 lookup_value 是数字，但 lookup_array 将同样的值存为文本（或相反），XLOOKUP() 将找不到匹配，即便两者在屏幕上看起来完全相同。

修复方法：检查 lookup_value 与 lookup_array 是否使用相同的数据类型。存为文本的数字通常在单元格中左对齐，而非右对齐。可用 VALUE() 将文本转换为数字，或根据不匹配方向使用TEXT() 反向转换。

溢出错误

当您的 return_array 覆盖多列，但 XLOOKUP() 需要的单元格并非空白时，就会发生这种情况。Excel 需要在公式单元格旁留出可供溢出的空位；若已有内容，整个公式会失败。

修复方法：清空需要放置结果的单元格，然后输入公式。如果这些单元格中必须保留内容，请把 XLOOKUP() 公式移到周围有空位的其他位置。

总结

现在，您已经学会使用 XLOOKUP() 查找单个值、返回多列、处理缺失数据，甚至在两个范围之间进行嵌套查找。覆盖的内容相当全面。

今后，请将 XLOOKUP() 作为新建工作簿的默认选择。它比 VLOOKUP() 更灵活、比 INDEX/MATCH() 更易读，而且无需为每个公式都再包一层 IFERROR()。

如果您想进一步深入，单独学习 INDEX() 与 MATCH() 也很值得。