Excel 中的 XMATCH 教程：概览、语法与实用应用

在进行数据分析时，您常常需要在数组或单元格区域中，根据某个给定的值或模式，找出某个项的相对位置。

在 Excel 中进行模式匹配操作，最有效的方式是使用 XMATCH 函数。XMATCH 简洁而强大，具备多项特性，能够实现精确且灵活的匹配。

本教程将介绍 Excel XMATCH 函数的基础知识，解析其语法和各参数，并通过实际示例展示其功能。我们还会将 XMATCH 与另一常用于类似任务的函数 MATCH 进行比较。最后，我们将梳理使用 XMATCH 的常见陷阱，并给出最佳实践，确保稳健高效地使用该函数。

如果您正在学习 Excel 并希望进一步提升，欢迎查看我们的Excel 基础技能路径，系统学习必备要点。

什么是 XMATCH？

XMATCH 是一款灵活而稳健的匹配函数。XMATCH() 可执行多种匹配操作，从纵向与横向查找，到精确、近似与部分匹配。

对于长期使用 Excel 的用户来说，您可能已经很熟悉 MATCH 函数。在引入 XMATCH 之前，MATCH 一直是匹配任务的首选。您可以在我们的Excel 查找函数教程中了解相关内容。

不过，正如我们稍后将看到的，MATCH 在某些场景下会显得力不从心。在这方面，XMATCH 可视为对传统 MATCH 的改良与期待已久的升级，支持任意方向搜索和多种匹配类型，更易用且适用范围更广。

请注意，该函数仅在 Microsoft 365 版 Excel 和 Excel 2021 中可用。早期版本的用户仍只能使用 MATCH。

下面让我们来看一下 XMATCH 的语法与参数。

XMATCH 的语法与参数

以下是 XMATCH 函数的基本语法：

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])技术支持

该函数包含两个必需参数（即 lookup_value 和 lookup_array），以及两个可选参数（match_mode 和 search_mode），用于实现更精细和灵活的操作。

以下是四个参数的说明：

• Lookup_value：要查找的值。
• Lookup_array：进行搜索的数组或列表。可以是纵向或横向的查找数组。
• Match_mode：用于选择匹配类型。共有四种模式，包括：
• 0：精确匹配（默认模式）
• -1：精确匹配或下一个较小项
• 1：精确匹配或下一个较大项
• 2：通配符匹配，如 * 和 ?。
• Search_mode：用于指定搜索模式，包括搜索方向与算法。共有四种取值：
• 1：从首到尾搜索（默认值）
• -1：从尾到首搜索（反向搜索）
• 2：二分搜索（升序）。假定查找数组按升序排列，否则将返回无效结果。
• -2：二分搜索（降序）。假定查找数组按降序排列，否则将返回无效结果。

匹配模式：精确与灵活

XMATCH 提供可选参数 match_mode，让您选择匹配类型。这些选项非常实用，既能进行精确匹配，也能实现近似匹配。下面我们详细分析可用的匹配模式：

精确匹配

精确匹配（match_mode = 0）是 XMATCH 的默认模式。这意味着 Excel 会在查找数组中找到与查找值完全一致的项，否则返回 NA 错误。当准确性与精度至关重要时（例如在数据库中查找特定记录），精确匹配尤为关键。

下面是一个基本示例，演示如何使用 XMATCH 在DataCamp 课程目录中的技术列表里找到“Excel”的准确位置。我们在 D3 中提供查找值，在 A3:A14 中提供查找数组，Excel 正确返回位置 6。

近似匹配

在某些情况下，您可能需要更灵活的匹配方式，要么因为您并不确切知道查找值，要么因为您希望得到近似结果。

幸运的是，XMATCH 提供了两种模式，可用于“精确或次小值”和“精确或次大值”的匹配。

在下例中，我们使用近似匹配来查找最接近查找值 15 的课程数量。

当使用“次小值”（match_mode = -1）时，Excel 返回位置 6，因为 12 是列表中最接近且小于 15 的数；而对于“次大值”，Excel 返回 5，因为 16 是最接近且大于 15 的数。

我们也用精确匹配演示当 Excel 找不到完全匹配时会发生什么。

通配符与正则匹配

XMATCH 的一项创新功能是：当 match_mode 设为 2 时可使用基础通配符，设为 3 时可使用更高级的正则表达式。这些匹配模式仅适用于文本数据。

XMATCH 支持两种常见通配符：

• ?：匹配任意单个字符。
• *：匹配任意长度的字符序列。

下面是一些使用正则匹配的示例。第一个示例查找以字母 E 开头的技术；第二个示例查找名称中包含字母 A 的技术；最后一个示例查找三个字母的技术名。

请注意，除非另有指定（见下一节），XMATCH 将始终返回查找数组中的首次匹配项。

如果您希望使用更复杂的正则表达式，请将 match_mode 设为 3。正则表达式（常简写为 "regex" 或 "regexp"）用于定义搜索模式，可用于搜索、解析以及替换文本等任务。

关于模式匹配的更多细节，请参阅我们的Excel 正则教程：用正则表达式掌握模式匹配，开启系统学习。

这里有一个相对简单的例子：我们要找到课程代码中，开头三个字母、中间两位数字（31）、末尾三字母的课程所在位置。

XMATCH 的搜索模式：方向与效率

XMATCH 引入了一个新参数，可用于控制搜索方向。当您更关心最后一次出现的位置而非第一次出现时（例如在某些时间序列分析任务中），这一功能非常有用。

让我们详细了解不同的搜索模式。

反向搜索

要执行反向搜索，可将 search_mode 参数设为 -1。如下图所示，我们演示如何找到用户 Peter 完成的第一门与最后一门课程。

二分搜索

最后，search_mode 参数还支持在匹配操作中使用二分搜索算法。二分搜索是一种通过不断将搜索区间对半分割来工作的搜索算法。它是效率极高的搜索方法之一，但要求输入数据事先有序。

XMATCH 支持在升序（search_mode = 2）或降序（search_mode = -2）排序的数组上使用二分搜索。若查找数组未排序，函数将抛出错误。

XMATCH 的实用场景与示例

XMATCH 适用于从基础到高级的大量 Excel 任务。本节将介绍一些最具代表性的实用场景。

结合 INDEX/XMATCH 的二维查找

INDEX 与 XMATCH 可以配合执行二维查找，即同时在行与列中进行查找。通过组合这两个函数，我们可以创建动态查找，这在诸如财务分析等领域非常常见，常需要跨类别对比变量。

例如，下图中我们组合使用 INDEX 与 MATCH 来提取第二季度 Excel 课程的注册用户数。此处 INDEX 接受三个参数：查找值所在的数组、行号与列号。后两个参数的值由多个 XMATCH 函数提供。

如果我们想更换技术类别或季度，只需更改 XMATCH 函数中的查找值，INDEX 的结果也会随之更新。

INDEX/XMATCH 的工作方式与 INDEX/MATCH 非常相似。若您想了解这一强大组合的更多实战示例，请查看我们的在 Excel 中通过 INDEX MATCH 满足多个条件教程。

多列查找

XMATCH 的另一个绝佳用法是结合多重条件，实现多列查找。虽然 XMATCH 表面上只需要一个查找值与一个查找数组，但我们可以在 XMATCH 公式内部利用布尔逻辑构建临时数组，用 0 与 1 表示满足条件的行。

例如，您想找到列表中每个季度注册用户都少于 1000 的第一个技术项。可以用 * 号将每一列的条件结果相乘进行合并。Excel 会在底层定位到全为 1 的那一行，在我们的示例中是 Tableau，因此返回位置 5。

动态数组筛选

XMATCH 还可以与 FILTER 和 ISNUMBER 函数配合，增强动态数组筛选能力。

下面展示其工作方式。我们将使用这些函数，按用户名筛选用户的已完成课程信息。

首先，我们使用 XMATCH 根据用于筛选的姓名查找数组，计算表中每位用户的相对位置。这样会得到一列相对位置，其中不在筛选名单中的用户为 null。

接着，使用 ISNUMBER 将包含相对位置与 null 的列转换为 TRUE 或 FALSE。

最后，使用 FILTER 选择需要显示的所有列，并用包含 TRUE 与 FALSE 的列来筛选行。

使用 ISNA 处理空值

当 XMATCH 找不到任何匹配时，会返回 NA 错误。尽管这已具有提示性，但您可以借助 ISNA 与 IF 函数美化结果，避免误读。

设想我们有一张表，记录了 Sam 与 Peter 完成的课程，且我们想知道他们的共同课程。若仅使用 XMATCH，Excel 会返回共同课程的相对位置，否则为 null。若将 XMATCH 作为 ISNA 的参数，函数将返回 TRUE 或 FALSE。随后借助 IF 函数，我们可以将布尔值替换为自定义消息。

在大型数据集上使用二分搜索

我们已经提到，XMATCH 支持在匹配操作中使用二分搜索算法。当处理大型数据集时，这一功能可能带来极大改变。只要查找数组已排序，选择二分搜索引擎就能显著加速匹配操作。

XMATCH 与 MATCH：关键差异

如前所述，XMATCH 的设计初衷是取代传统的 MATCH 函数。两者用途相同，但 XMATCH 新增了一系列功能，使其更为强大。以下列出了 XMATCH 与 MATCH 的主要差异：

常见陷阱与解决方案

XMATCH 的用法很多。然而，了解常见问题也同样重要。以下列出一些常见陷阱及应对建议。

• 检查数据类型：若在数据类型不正确的区域上应用 XMATCH，会产生意外结果。因此，在使用函数前，请确保列的数据类型设置正确。
• 范围一致性：虽然 XMATCH 支持横向与纵向查找范围，但混用可能导致意外问题与性能下降。尽量保持一致，选择横向或纵向之一。
• 充分利用匹配模式：XMATCH 提供多种匹配模式。默认是精确匹配，您也可以通过近似搜索与正则操作来微调查找。
• 有序数组表现更佳：总体而言，将 XMATCH 应用于已排序数组，性能会更高。这对默认函数同样成立；若要使用二分搜索，更是必备前提。
• 处理 N/A 错误：如前所述，若无匹配项，XMATCH 会返回 NA 错误。最佳做法是将结果传入 IF 与 ISNA 的组合，或使用 IFNA，以提升可读性。

性能考量

在 Excel 中，执行查找操作的函数不止 XMATCH。例如，您也可以考虑使用VLOOKUP、HLOOKUP，乃至MATCH等传统函数。然而，就搜索速度而言，XMATCH 可以说是最快的，尤其是在有序数组上，还能进一步利用二分搜索的优势。

另需说明的是，XMATCH 被设计为动态数组公式，这意味着它可根据输入数据返回可变大小的数组。动态数组公式在处理大型数据集或复杂计算时尤为适用。

结语

恭喜您读到这里。XMATCH 是一项强大的函数，在 Excel 数据分析中不可或缺。无论单独使用，还是与其他 Excel 函数配合，它都能将您的查找操作提升到新水平。

不过，Excel 的精彩世界还有更多值得探索。欢迎查看我们的课程、博客与教程，成为 Excel 高手：

• XLOOKUP() 与 VLOOKUP()：Excel 用户对比指南
• Excel 数据处理速查表
• Excel｜高级技巧精通课程
• Excel 进阶 Power Query
• Excel 快捷键速查表
• Excel 公式速查表
• 如何在 Excel 中用 INDEX MATCH 满足多个条件
• 在电子表格中用 INDEX-MATCH 进行数据整理
• Excel 正则教程：用正则表达式掌握模式匹配