EXCEL高级函数应用-XMATCH函数

在 Excel 数据处理中，“精准定位数据位置” 是高频需求 —— 比如在 1000 行订单中找 “最后一笔北京地区订单” 的行号，在重复的员工名单中定位 “第 3 次出现的张三”，或是按 “大于等于” 条件找 “首个达标销量” 的位置。过去我们依赖 MATCH 函数，但它仅支持基础定位，面对复杂场景需嵌套多个函数。而 Excel 365/2021 推出的XMATCH 函数，直接升级为 “全能定位工具”：支持反向查找、重复值定位、多条件匹配，还能自定义匹配规则，堪称 “MATCH 函数的升级版王者”。今天就带大家全面掌握这个高效函数！

一、先吃透：XMATCH 函数的基本语法与参数

XMATCH 函数的核心是 “在指定区域中查找目标值，返回其相对位置”，语法在 MATCH 基础上新增关键参数，灵活性大幅提升。需重点理解 “匹配模式” 和 “搜索模式” 的组合用法。

1. 基本语法

XMATCH(lookup\_value, lookup\_array, \[match\_mode], \[search\_mode], \[by\_col], \[if\_not\_found])

括号中带[]的参数为可选项，日常基础查找中，前 2 个参数是 “必选项”，后 4 个参数根据场景灵活添加（默认值：match_mode=0，search_mode=1，by_col=FALSE，if_not_found=#N/A）。

2. 参数详细说明

结合 “订单数据定位” 场景，参数含义拆解如下，每个参数都标注 “核心作用” 和 “通俗举例”，避免理解偏差：

参数名称	作用解释	通俗举例（订单表定位场景）	是否必选
lookup_value	要查找的 “目标值”（可是文本、数字、单元格引用，或计算结果）	查找 “北京”“5000 元” 或 A2 单元格的订单号	是
lookup_array	要搜索的 “数据区域”（支持单行、单列或二维区域，无需固定维度）	订单表的 “地区列”（D 列）、“金额列”（E 列）	是
[match_mode]	匹配规则（4 种模式）：0 = 精确匹配（默认），1 = 近似匹配（升序，找≤值），-1 = 近似匹配（降序，找≥值），2 = 通配符匹配	用 “京” 模糊找含 “京” 的地区，选match_mode=2	否
[search_mode]	搜索方向 / 方式（4 种模式）：1 = 从首到尾（默认），-1 = 从尾到首，2 = 二分法升序，-2 = 二分法降序	找最后一笔北京订单，选search_mode=-1	否
[by_col]	二维区域搜索时的方向：TRUE = 按列搜索，FALSE = 按行搜索（默认）	二维区域中按列找 “北京”，选by_col=TRUE	否
[if_not_found]	无匹配结果时返回的内容（默认返回 #N/A 错误）	无北京订单时，显示 “暂无匹配数据”	否

关键提醒：

1. lookup_array支持二维区域（如 A1:C10），而 MATCH 仅支持一维区域，这是 XMATCH 的核心优势之一；

2. match_mode和search_mode组合使用时，需确保逻辑一致（如match_mode=1需配合 “升序数据”，search_mode=2也需数据升序）。

二、实战练：按功能场景拆解 XMATCH 使用示例

XMATCH 的灵活性体现在 “多模式组合”，下面用 7 个高频场景示例，覆盖 “精确匹配、模糊匹配、反向查找、重复值定位” 等核心需求，每个示例均标注 “公式 + 解析 + 注意事项”，确保可直接套用。

示例 1：基础精确匹配（替代 MATCH，更简洁）

需求：在 “产品表” 的 A2:A20（产品编号列，无序数据）中，查找 “P015” 对应的位置（相对行数）。

公式：

\=XMATCH("P015", A2:A20)

解析：

• 省略match_mode时，默认match_mode=0（精确匹配），无需像 MATCH 那样手动写 “0”；

• lookup_value="P015"：目标查找值；lookup_array=A2:A20：产品编号列；

• 结果：若 A8 单元格是 “P015”，则返回 7（因为 A2 是区域第 1 位，A8 是第 7 位）。

对比 MATCH：MATCH 需写=MATCH("P015", A2:A20, 0)，XMATCH 省略默认参数后更简洁，降低输入错误概率。

示例 2：自定义无结果提示（避免 #N/A 错误）

需求：在 “员工表” 的 B2:B50（姓名列）中，查找 “李四” 的位置，若无此员工，显示 “未录入”。

公式：

\=XMATCH("李四", B2:B50, , , , "未录入")

解析：

• 新增if_not_found="未录入"参数，直接自定义无匹配结果的提示；

• 中间省略的参数（match_mode“search_mode”“by_col”）均按默认值处理；

• 结果：若 B 列无 “李四”，则显示 “未录入”，而非刺眼的 #N/A 错误，报表更美观。

注意事项：if_not_found参数需放在第 6 位，前面省略的参数需用逗号占位（如公式中的 “,,,,”），否则参数位置错乱会导致结果错误。

示例 3：通配符模糊匹配（查找含关键词的内容）

需求：在 “供应商表” 的 C2:C30（供应商名称列）中，查找 “名称包含‘科技’” 的供应商位置（如 “北京 XX 科技”“科技发展公司”）。

公式：

\=XMATCH("\*科技\*", C2:C30, 2)

解析：

• match_mode=2：开启 “通配符匹配” 模式，此时lookup_value中的*（任意字符）、?（单个字符）生效；

• lookup_value="*科技*"：*代表 “任意多个字符”，即匹配 “包含‘科技’的所有文本”；

• 结果：返回首个含 “科技” 的供应商在 C2:C30 中的相对位置（如 C10 是首个匹配项，返回 9）。

场景延伸：若要找 “以‘北京’开头” 的供应商，lookup_value改为 “北京 *”；找 “第二个字符是‘海’” 的供应商，改为 “? 海 *”。

示例 4：反向查找（从末尾开始找最后一个匹配值）

需求：在 “销售记录表” 的 D2:D100（地区列，含重复值）中，查找 “最后一笔上海地区订单” 的位置（避免手动翻到表格末尾）。

公式：

\=XMATCH("上海", D2:D100, 0, -1)

解析：

• match_mode=0：精确匹配 “上海”；

• search_mode=-1：从lookup_array的 “最后一行向第一行” 搜索，即找最后一个匹配值；

• 结果：若 D95 是最后一个 “上海”，则返回 94（D2 是第 1 位，D95 是第 94 位）。

对比 MATCH：MATCH 仅支持从首到尾搜索，需嵌套 INDEX+ROW 才能找最后一个匹配值，而 XMATCH 用search_mode=-1一步实现，效率大幅提升。

示例 5：近似匹配（查找大于等于目标值的首个位置）

需求：在 “业绩表” 的 E2:E50（销售额列，降序排列：10000、8500、7000、5000…）中，查找 “首个≥6000 元” 的销售额位置。

公式：

\=XMATCH(6000, E2:E50, -1)

解析：

• match_mode=-1：近似匹配（需数据降序排列），查找 “大于等于 lookup_value 的最小值”；

• 数据背景：E 列降序排列，确保match_mode=-1能正确定位（若数据无序，需先排序或用search_mode=1）；

• 结果：E 列中≥6000 的最小值是 7000（假设在 E4），则返回 3（E2 是第 1 位，E4 是第 3 位）。

关键提醒：match_mode=1（找≤值）需数据升序，match_mode=-1（找≥值）需数据降序，否则会返回错误结果。

示例 6：二维区域匹配（按列 / 按行查找，无需转置）

需求：在 “季度业绩表” 的 A1:D5（二维区域，A1 是标题 “员工”，B1:D1 是 “Q1-Q3”，A2:A5 是员工姓名）中，按列查找 “张三” 在哪个季度的业绩列中（即找 “张三” 所在的列位置）。

公式：

\=XMATCH("张三", A1:D5, 0, 1, TRUE)

解析：

• lookup_array=A1:D5：首次支持二维区域（4 列 5 行），无需像 MATCH 那样先转置区域；

• by_col=TRUE：按 “列” 搜索（默认by_col=FALSE是按行搜索）；

• 结果：若 “张三” 在 A3 单元格（第 3 行第 1 列），则返回 1（代表第 1 列）；若在 C4（第 4 行第 3 列），则返回 3（代表第 3 列）。

场景价值：处理跨列跨行的二维数据时，无需手动调整区域维度，直接按列 / 按行查找，减少操作步骤。

示例 7：多条件匹配（组合条件定位目标值）

需求：在 “订单表” 的 A2:B100（A 列是 “地区”，B 列是 “金额”）中，查找 “地区 = 北京且金额 = 5000” 的订单位置（即同时满足两个条件的行）。

公式：

\=XMATCH(1, (A2:A100="北京")\*(B2:B100=5000), 0)

解析：

• 多条件用*（逻辑 AND）连接：(A2:A100="北京")返回 TRUE/FALSE 数组，(B2:B100=5000)也返回数组，两者相乘后，同时为 TRUE 的行结果为 1（TRUE=1，FALSE=0）；

• lookup_value=1：查找相乘后结果为 1 的位置，即同时满足两个条件的行；

• 结果：返回首个 “北京 + 5000 元” 订单在 A2:B100 中的相对行位置（如第 15 行满足条件，返回 14）。

拓展：若需 “满足其一”（地区 = 北京或金额 = 5000），将*改为+，lookup_value改为 1 或 2（因为 TRUE+TRUE=2，TRUE+FALSE=1）。

三、总结：XMATCH 函数的核心优势与注意事项

1. 核心优势（对比 MATCH 函数）

对比维度	XMATCH 函数	MATCH 函数
区域支持	支持一维 / 二维区域，无需转置	仅支持一维区域（单行 / 单列）
参数灵活性	6 个参数，支持自定义无结果提示、二维查找	3 个参数，无自定义提示、二维查找
匹配模式	4 种（含通配符），覆盖更多场景	3 种（无通配符），场景有限
搜索方向	支持从尾到首反向搜索	仅支持从首到尾正向搜索
多条件匹配	可直接组合条件，无需嵌套过多函数	需嵌套多个函数，公式复杂

2. 必记注意事项

• 版本要求：仅支持 Excel 365、Excel 2021 及以上版本，低版本（如 2019、2016）无此函数，会返回 #NAME? 错误；

• 参数位置：可选参数需按顺序填写，省略的参数需用逗号占位（如XMATCH("北京", D2:D100, , -1)），否则会导致参数错位；

• 数据排序：近似匹配（match_mode=1/-1）和二分法搜索（search_mode=2/-2）需确保数据按对应顺序排列（升序 / 降序），无序数据会返回错误；

• 文本大小写：默认不区分大小写（如 “北京” 和 “北京” 视为相同），若需区分，需嵌套 EXACT 函数（如XMATCH(TRUE, EXACT(A2:A10, "北京"), 0)）。

掌握 XMATCH 函数，能解决 MATCH 无法覆盖的 “反向查找、二维匹配、多条件定位” 等复杂场景，尤其适合处理海量数据或动态报表。建议打开 Excel，用自己的表格试试上面的示例，体验 “一步定位” 的高效，告别繁琐的手动查找和嵌套公式！