Excel 中的 XLOOKUP 函数

VLOOKUP 和 HLOOKUP 函数有着它们的重要性与普及度。这些函数如此受欢迎的原因之一是，过去当用户想要在 Excel 中查找任何值时，并没有可用的选项。对于复杂的公式，他们不得不依赖 INDEX MATCH 组合。但现在已经不一样了！

在 Excel 的新版本，即 Excel 365 或 Excel 2021 中，您不再需要选择上述函数——因为 Microsoft 推出了一款更强大、更 robust 的函数，它克服了 VLOOKUP 令人讨厌的错误和所有不足之处，**那就是 XLOOKUP 函数**。

在很多方面，它比其他查找函数都好。它可以垂直和水平查找，向左或向上查找，捕捉到使用多个条件搜索的能力。

在本教程中，我们将涵盖以下主题

1. XLOOKUP 函数：语法、参数、返回值
2. VLOOKUP 与 XLOOKUP 的区别
3. 如何在 Excel 中实现 XLOOKUP：示例
   • XLOOKUP 垂直和水平查找
   • XLOOKUP 向左查找
   • XLOOKUP 精确匹配和近似匹配
   • XLOOKUP 使用通配符
   • XLOOKUP 查找最后一个匹配项
   • XLOOKUP 返回多个值（行或列）
   • XLOOKUP 多条件查找
   • 双向 XLOOKUP
   • XLOOKUP 错误处理
   • 区分大小写的 XLOOKUP
4. XLOOKUP 函数不起作用

XLOOKUP 函数：语法、参数、返回值

"Excel 中的 XLOOKUP 函数在一个范围或数组中查找给定值，并从另一列返回相应的值。"

使用此函数的好处是，它可以查找表中的两个位置，即垂直和水平，并且可以执行精确匹配（默认）、近似匹配（找到最接近的数据）或通配符匹配（使用通配符字符进行部分匹配）。

语法

参数

1. Lookup_value（必需）——此参数表示要在表中查找的值。
2. Lookup_array（必需）——此参数表示要在其中搜索的表、范围或数组。
3. Return_array（必需）——它表示要从中返回值的范围或数组。
4. If_not_found [可选]——这是一个可选参数，表示在找不到匹配项时将返回的值。如果您跳过此参数，则会返回 #N/A 错误。
5. Match_mode [可选]——此参数可以根据您的需求接受四个值；如果您跳过此参数，则默认值为 0，并返回精确匹配。
   • 0——如果您想要精确匹配，可以输入此值。
   • -1——如果您要查找精确匹配或下一个较小值，可以输入此值。如果找不到精确匹配，则返回下一个较小的值。
   • 1——如果您想要返回精确匹配或下一个较大值，可以输入此值。如果找不到精确匹配，则返回下一个较大值。
   • 2——此值表示通配符字符匹配。
6. Search_mode [可选]——此参数帮助用户管理搜索方向。如果您跳过此参数，则默认值为 1，并从数组的第一个位置开始搜索到最后一个位置。它可以接受以下值
   • 1 - 输入此值将从定义数组的第一个位置开始搜索到最后一个位置。
   • -1 - 输入此值将反向搜索，即从最后一个位置搜索到第一个位置。
   • 2 - 如果要进行二进制搜索且数据按升序排序，则使用此值。
   • -2 - 如果要进行二进制搜索且数据按降序排序，则使用此值。

返回值

XLOOKUP 函数在一个范围或数组中搜索预定义的值，并从另一列返回相关的值。

XLOOKUP 函数如何工作？

为了理解 XLOOKUP 函数的工作原理，我们将使用查找公式来执行精确查找。

例如，我们有 5 位不同销售人员的姓名及其月销售额。现在使用 XLOOKUP，我们需要获取候选人的销售报告。单元格 B1 中的姓名代表 lookup_value，现在我们将此值在我们的 lookup_array (A2:A10) 中进行查找，并根据位置，它将从 return array (B2:B10) 中返回值。我们的公式变为

没有烦人的列索引号问题，无需排序，也没有其他麻烦。只需一个公式即可获得结果！

XLOOKUP vs. VLOOKUP

与传统的 VLOOKUP 相比，XLOOKUP 具有许多优势。下面是一个比较表，列出了 XLOOKUP 和 VLOOKUP 之间的区别

如何在 Excel 中实现 XLOOKUP：示例

示例 1：垂直和水平查找

在早期版本（Excel 365 之前）中，如果用户想垂直或水平查找，他们必须为不同的查找类型实现两个函数：VLOOKUP 函数用于垂直查找，HLOOKUP 用于水平查找。

幸运的是，随着 Excel 2021 或 Excel 365 的推出，Microsoft 推出了 XLOOKUP 函数，它能够用一个语法执行这两种操作。您需要做的就是提供相应的查找和返回数组。

例如，下面是数据样本，其中包含 ID、医院项目及其可用状态。

应用 VLOOKUP

1. 键入以下 XLOOKUP 公式。

1. 按 Enter 键。它将从垂直表中查找产品。

应用 HLOOKUP

1. 键入以下 XLOOKUP 公式。这样，我们不是提供垂直的数据数组，而是提供水平的数据数组。

1. 按 Enter 键。它将从水平表中查找产品。

示例 2：使用 XLOOKUP 执行左侧查找

对于早期的 Excel 版本，没有直接的公式可以向左查找数据。您必须组合 Index 和 Match 函数才能在 Excel 工作表中应用左侧查找。但有了 XLOOKUP 函数，您就不再需要组合 Index 和 Match 函数了。您需要做的就是指定查找表，XLOOKUP 将处理其余任务。

例如，在上述数据中，我们在左侧添加了另一个列 List_number。如果我们知道了产品名称，并被要求查找 list_number，该怎么办？

在这种情况下，我们可以使用 XLOOKUP 函数来应用左侧查找。请按照以下步骤进行左侧查找

1. 键入 XLOOKUP 函数并指定其参数。公式将如下所示

1. 按 Enter 键。它将快速应用左侧查找，并从 List_Number 表中获取输出。

使用相同的步骤，您可以水平实现行的左侧查找！

示例 3：执行精确匹配和近似匹配

在本教程的开头，我们已经介绍了 XLOOKUP 函数使用的参数。match_mode 参数控制匹配行为。它有四种选项

• 0 - 如果要精确匹配，请输入此项。
• -1 - 如果要查找精确匹配或下一个较小值，请输入此值。如果找不到精确匹配，则返回下一个较小的值。
• 1 - 如果要返回精确匹配或下一个较大值，请输入此值。如果找不到精确匹配，则返回下一个较大值。
• 2——此值表示通配符字符匹配。

精确匹配 XLOOKUP

在大多数情况下，您将在 Microsoft Excel 中使用精确查找。因此，它是默认属性，如果您不在 match_mode 参数中传递任何值，它将隐含地取 0。因此，您可以跳过 match_mode，仅提供前三个必需参数。

1. 键入 XLOOKUP 函数并指定其参数。公式将如下所示

1. 按 Enter 键。它将快速应用精确的 XLOOKUP 查找并从表中获取输出。由于“Stop Ca”不在我们的表中，它将返回“Not Found”。

近似匹配 XLOOKUP

要执行近似查找匹配，只需将 match_mode 参数设置为 -1 或 1，具体取决于您是想返回下一个较小还是较大数据的近似值。

例如，在这里，我们有一个包含成绩下限的查找表。因此，我们将 match_mode 设置为 1，以便在找不到精确匹配时搜索下一个较大值

1. 键入 XLOOKUP 函数并指定其参数。公式将如下所示

1. 按 Enter 键。它将快速应用精确的 XLOOKUP 查找并从表中获取输出。由于“Stop Ca”不在我们的表中，因此它将查找下一个较大值并返回 Stop Cap 的 ID 号。

示例 4：匹配部分数据（通配符）

XLOOKUP 提供了执行部分匹配查找的选项。您需要做的就是将 match_mode 参数设置为 2。主要地，大多数用户倾向于使用以下通配符字符

1. 星号 (*) - 此通配符用于定义字符序列。
2. 问号 (?) - 此通配符用于定义任何单个字符。

让我们通过一个实际示例来实现部分匹配。

上表列出了手机的特性、存储、价格、显示屏等。您对特定智能手机的价格感兴趣。唯一的问题是您只知道手机的名称，而不是与 A 列中完全相同的完整型号名称。解决方案是输入手机名称，并用通配符替换前后字符。

假设我们要获取 Samsung 公式手机的价格信息，我们将使用通配符并设置 match_mode 参数为 2 来包含以下公式

上述公式将通过部分匹配表中的 Samsung 来返回价格

注意：您可以直接在双引号（“ ”）中输入手机名称，而不是单元格引用。

示例 5：反向工作以获取最后一个匹配项

有时，您的查找表包含查找值的多个实例。默认情况下，XLOOKUP 公式会获取查找值的第一个匹配项的输出。但是，如果您想返回最后一个匹配项怎么办？要完成此操作，您只需将 Xlookup 公式设置为反向搜索。

您可以通过名为 search_mode 的第 6 个参数来控制搜索顺序

• 1（默认值）- 如果要将搜索从第一个值设置到最后一个值，请输入此项。
• -1 - 如果要将搜索设置为反向顺序或要获取从最后一个到第一个的值，请使用此项。

例如，让我们实现 XLOOKUP 公式以返回产品 'STERI SET' 的最后一个销售值。与上述公式不同，我们将前三个必需参数（E3 用于 lookup_value 参数，B2:B11 用于 lookup_array，C2:D11 用于 return_array 参数）组合在一起，这次我们将第 5 个参数设置为 -1。

结果，它将搜索查找值“STERI SET”的最后一个出现，并返回最低价格。

示例 6：返回多个列或行

您知道 XLOOKUP 函数不仅限于返回单个值吗？使用此公式，您可以返回与同一 lookup_value 相关的多个值。要实现这一点，您只需输入前三个必需参数，而无需任何其他操作！

例如，假设在上述（智能手机数据）表中，您被要求获取特定手机的所有详细信息。要实现的公式非常简单。您需要做的不是单个列，而是为 return_array 参数提供整个范围 (B1: G6)

您需要做的就是输入公式，Ms. Excel 将自动将输出分散到相邻的空白单元格中。在我们的例子中，它返回了所有六列。请参阅以下输出

使用 Transpose 函数，您也可以将上述输出垂直显示在一列中。将 XLOOKUP 嵌套到 Transpose 中将自动垂直翻转数组。

您将获得以下输出

注意：在使用此公式之前，请确保您的公式单元格右侧或下方有足够的空白单元格。因为多个值会调整到相邻单元格，如果 Excel 发现任何数据，它将返回 #SPILL! 错误。

示例 7：XLOOKUP 多条件查找

XLOOKUP 的又一个惊人功能是它原生处理数组。由于这个特性，它可以轻松地直接在 lookup_array 参数中使用多个条件。请遵循以下用于多个条件的语法

每个条件的输出都是 TRUE 或 FALSE 值的数组。但是，由于我们相乘了数组，TRUE 或 FALSE 布尔值分别转换为 1 和 0，从而生成了最终的查找数组。根据基本的数学规则，如果您将任何东西与 0 相乘，输出将始终为零；因此，在查找表中，只有满足所有条件的项才用 1 表示。

您可以在下表中看到，我们有三个条件需要检查。根据这些条件，我们从 E2:E11（return_array）中找到了销售额。

我们将应用三个条件，它们是

1. 条件 1（sales_person）= H2
2. 条件 2（List_number）= H3
3. 条件 3（Products）= H4

公式的形状如下

结果，XLOOKUP 函数将处理所有条件，如果满足每个条件，它将从返回数组中查找输出。

示例 8：双向 XLOOKUP

双向查找通常用于查找给定行和列的交叉点值。令人惊讶的是，Excel XLOOKUP 也能做到这一点！所有它需要的是将 XLOOKUP 嵌套到另一个 XLOOKUP 中，您将获得以下语法

在这个例子中，我们有三个月的销售报告，即 S1、S2、S3。在这里，我们将查找特定销售人员在 S3 季度的销售报告。要解决这个问题，我们将使用双向 Xlookup 和以下公式

示例 9：区分大小写的 XLOOKUP

在上面的例子中，您可能已经注意到 XLOOKUP 函数将小写字母和大写字母视为相同。默认情况下，此函数区分大小写。为了使 XLOOKUP 区分大小写，我们将使用 EXACT 函数替换 lookup_array 参数。使用以下语法

顾名思义，EXACT 函数的作用是比较给定的查找值与数组中的每个值；如果匹配（包括字母大小写），则返回 TRUE。现在，XLOOKUP 将其查找值与查找数组进行匹配，如果两者都为 TRUE，它将搜索给定数组的 TRUE，并从返回数组中返回匹配项。

假设我们要以区分大小写的方式获取产品 STERI SET 的销售数据，我们将使用以下公式

由于它找到了精确匹配，它将从返回数组中返回销售值，

注意：如果您的查找数组包含两个或多个相同的数据，XLOOKUP 函数将返回第一个匹配项的值。

示例 10：XLOOKUP 错误处理

如果找不到查找值，XLOOKUP 函数将返回 #N/A 错误。在专业地使用公式时，#N/A 错误看起来不好，甚至可能让许多中级 Excel 用户感到困惑。因此，捕获错误并写入用户友好的注释始终是明智的。

例如，让我们考虑找不到匹配项的情况。

结果，您收到一个 #N/A 错误。要将错误替换为您自定义的消息，您需要在第四个参数中添加文本。

例如，如果有人输入了无效的产品名称，我们的公式将返回“无效名称”的消息。

XLOOKUP 函数不起作用

很多时候，您的 Excel XLOOKUP 公式工作正常，但最终返回错误。这通常是由于以下原因之一

1. 您正在使用旧版 Excel 中的 XLOOKUP。

XLOOKUP 函数唯一的缺点是它不向后兼容，仅在 Microsoft 365 版 Excel 和 Excel 2021 中可用，不能在早期版本中使用。

2. Excel XLOOKUP 返回错误输出

有时，此 Excel 函数可能会返回错误输出。常见的原因是您输入了错误的数据值，或者在向下复制公式时 return_array 范围可能已移动。您可以通过在两个范围中使用绝对单元格引用（例如，$A$1:$A$20）来防止这种情况，这将锁定两个数组范围并返回正确输出。

3. XLOOKUP 函数返回 #N/A 错误

如果 XLOOKUP 函数在查找表中找不到查找值，则返回 #N/A 错误。但是，您可以将错误替换为您自定义的消息。

4. XLOOKUP 函数返回 #VALUE 错误

如果查找和返回数组的维度不兼容，则会出现 #VALUE! 错误。例如，如果您在参数中提供了水平的 lookup_array，并想返回垂直 return_array 的值。

5. XLOOKUP 函数返回 #REF 错误

当在两个不同的工作簿之间查找数据时，此函数会引发 #REF! 错误，其中一个工作簿是关闭的。要修复错误，只需打开两个文件。