Excel XMATCH函数详细介绍与实例教程

一、XMATCH函数概述

XMATCH是Excel 365和Excel 2021中引入的现代化查找函数，用于在数组或范围中搜索指定项，并返回该项的相对位置。它是传统MATCH函数的升级版本，提供了更强大的匹配功能和更好的灵活性。

基本语法

=XMATCH(查找值, 查找数组, [匹配模式], [搜索模式])

参数说明

参数	是否必需	说明
查找值	必需	要查找的值
查找数组	必需	要在其中搜索查找值的数组或范围
[匹配模式]	可选	指定匹配类型：0=精确匹配（默认），-1=精确匹配或下一个较小项，1=精确匹配或下一个较大项，2=通配符匹配
[搜索模式]	可选	指定搜索方式：1=从第一项开始搜索（默认），-1=从最后一项开始搜索，2=二分搜索（升序），-2=二分搜索（降序）

二、XMATCH与MATCH函数对比

对比维度	XMATCH	MATCH
默认匹配模式	0（精确匹配）	1（近似匹配）
搜索方向	支持从上到下或从下到上	只能从上到下
通配符支持	通过匹配模式参数2支持	仅当匹配模式为0时支持
二分搜索	内置二分搜索模式	不支持
性能	大数据集下更优	标准性能
版本支持	Excel 365, 2021+	所有版本

三、XMATCH基础应用实例

示例数据：产品销售表

产品ID	产品名称	类别	单价	销量
P001	笔记本电脑	电子产品	5999	125
P002	智能手机	电子产品	3299	342
P003	办公桌	家具	1899	67
P004	会议椅	家具	899	156
P005	咖啡机	电器	2599	43

实例1：基本精确查找

查找"智能手机"在B列中的位置：

=XMATCH("智能手机", B2:B6)

结果返回：2（第2行，因为B2是第一个数据）

实例2：从下往上查找

查找最后出现的"家具"类别位置：

=XMATCH("家具", C2:C6, 0, -1)

结果返回：4（因为"家具"出现在第3行和第4行，从下往上找到的是第4行）

四、XMATCH高级应用

1. 近似匹配应用（成绩等级查找）

分数下限	等级
0	F
60	D
70	C
80	B
90	A

查找85分在分数下限列中的位置（匹配下一个较小项）：

=XMATCH(85, A2:A6, -1)

结果返回：4（对应80分所在行）

结合INDEX获取等级：

=INDEX(B2:B6, XMATCH(85, A2:A6, -1))

结果返回："B"

2. 通配符匹配

查找以"手机"结尾的产品位置：

=XMATCH("*手机", B2:B6, 2)

结果返回：2（智能手机所在行）

3. 二分搜索优化

如果数据已按升序排序，可以使用二分搜索提高性能：

=XMATCH(3299, D2:D6, 1, 2)

注意：使用二分搜索前必须确保数据已按要求排序，否则结果可能错误。

五、XMATCH与其他函数组合应用

1. XMATCH + INDEX组合（替代VLOOKUP）

查找"P004"的产品名称：

=INDEX(B2:B6, XMATCH("P004", A2:A6))

结果返回："会议椅"

2. 双向查找（行列交叉）

	一季度	二季度	三季度	四季度
产品A	120	135	110	150
产品B	85	92	105	118
产品C	210	195	220	245

查找"产品B"在"三季度"的销量：

=INDEX(B2:E4, XMATCH("产品B", A2:A4), XMATCH("三季度", B1:E1))

结果返回：105

3. XMATCH + CHOOSE组合

根据员工绩效等级返回对应的奖金系数：

等级	A	B	C	D
奖金系数	1.5	1.2	1.0	0.8

已知员工等级为"B"，查找对应系数：

=CHOOSE(XMATCH("B", A2:D2), 1.5, 1.2, 1.0, 0.8)

或使用INDEX：

=INDEX(A3:D3, XMATCH("B", A2:D2))

结果返回：1.2

六、XMATCH的高级搜索功能

1. 查找最后一个匹配项

数据中出现多次相同值的情况：

日期	产品	销售额
2024-01-01	笔记本	12000
2024-01-02	手机	8500
2024-01-03	笔记本	13500
2024-01-04	平板	9200
2024-01-05	笔记本	11000

查找最后一次出现"笔记本"的位置：

=XMATCH("笔记本", B2:B6, 0, -1)

结果返回：5

2. 查找第一个和最后一个非空单元格

查找第一个非空单元格位置：

=XMATCH(TRUE, A:A<>"")

查找最后一个非空单元格位置：

=XMATCH(TRUE, A:A<>"", 0, -1)

七、XMATCH在数据验证中的应用

1. 检查值是否存在

判断"打印机"是否在产品列表中：

=ISNUMBER(XMATCH("打印机", B2:B6))

结果返回：FALSE（因为打印机不在列表中）

2. 返回自定义提示

=IF(ISNUMBER(XMATCH("咖啡机", B2:B6)), "存在", "不存在")

结果返回："存在"

八、XMATCH匹配模式详解

匹配模式	说明	数据要求	示例
0	精确匹配（默认）	无特殊要求	查找等于查找值的第一个值
-1	精确匹配或下一个较小项	查找数组必须降序排列	查找小于等于查找值的最大值
1	精确匹配或下一个较大项	查找数组必须升序排列	查找大于等于查找值的最小值
2	通配符匹配	支持*和?通配符	*表示任意字符序列，?表示单个字符

九、XMATCH搜索模式详解

搜索模式	说明	适用场景	性能
1	从第一项向最后一项搜索（默认）	查找第一个匹配项	线性搜索，数据量大时较慢
-1	从最后一项向第一项搜索	查找最后一个匹配项	线性搜索，数据量大时较慢
2	二分搜索（升序）	数据已升序排序，查找效率高	极快，O(log n)
-2	二分搜索（降序）	数据已降序排序，查找效率高	极快，O(log n)

二分搜索使用注意事项：

使用二分搜索前必须确保数据已正确排序
升序数据用2，降序数据用-2
如果数据未排序，结果将不可预测
大数据集时性能提升明显（如10万行以上）

十、XMATCH常见错误处理

错误类型	可能原因	解决方法
#N/A	查找值不在查找数组中	使用IFERROR处理；检查数据类型是否一致
#VALUE!	查找数组参数不正确	确保查找数组是单行或单列
#NUM!	二分搜索时数据未正确排序	改用线性搜索（模式1或-1）或确保数据已排序
数据类型不匹配	查找值和查找数组的数据类型不一致	使用TEXT函数统一格式；检查数字和文本格式

十一、XMATCH实用技巧

1. 多条件查找

查找同时满足"电子产品"且"单价>3000"的产品位置：

=XMATCH(1, (C2:C6="电子产品")*(D2:D6>3000))

注意：这是数组公式，需要按Ctrl+Shift+Enter（旧版本）或直接Enter（Excel 365）

2. 区分大小写查找

默认XMATCH不区分大小写。需要区分大小时，使用EXACT函数：

=XMATCH(TRUE, EXACT(A2:A6, "Product"))

3. 返回多个匹配位置

XMATCH默认只返回第一个匹配项。要返回所有匹配项，可使用FILTER函数：

=FILTER(ROW(A2:A6)-1, A2:A6="笔记本")

十二、综合应用实例

实例：员工信息查询系统

工号	姓名	部门	职位	入职日期	基本工资
E001	张三	销售部	销售经理	2020-03-15	8500
E002	李四	技术部	高级工程师	2019-07-22	12000
E003	王五	市场部	市场专员	2021-01-10	6800
E004	赵六	销售部	销售代表	2022-05-20	7200

创建动态查询：

输入工号：	E003
员工姓名：	=INDEX(B2:B5, XMATCH(B8, A2:A5))
所在部门：	=INDEX(C2:C5, XMATCH(B8, A2:A5))
职位：	=INDEX(D2:D5, XMATCH(B8, A2:A5))
入职日期：	=INDEX(E2:E5, XMATCH(B8, A2:A5))
基本工资：	=INDEX(F2:F5, XMATCH(B8, A2:A5))

实例2：动态数据验证列表

创建级联下拉菜单：

在数据验证中使用XMATCH确定所选类别的位置
使用OFFSET或CHOOSE根据类别返回对应的项目列表
使用XMATCH验证用户输入是否有效

十三、性能优化建议

场景	建议	说明
小数据集（<1000行）	使用默认设置	性能差异不明显
大数据集（>10000行）	使用二分搜索	确保数据已排序
频繁重复计算	避免整列引用（如A:A）	使用具体范围（如A2:A1000）
多条件查找	使用辅助列	将多个条件合并为一列

十四、XMATCH与XLOOKUP对比

对比项	XMATCH	XLOOKUP
返回值	位置序号	实际值
主要用途	定位、与INDEX配合	直接查找返回值
返回数组	单个数值	支持多列返回
错误处理	需要IFERROR嵌套	内置错误处理
组合使用	常与INDEX配合	可独立使用

推荐组合：INDEX + XMATCH 提供最大的灵活性，类似XLOOKUP但允许更复杂的操作。

十五、总结

XMATCH是MATCH函数的现代化升级，提供了更丰富的匹配和搜索选项
默认精确匹配更符合现代使用习惯，减少错误
双向搜索（从上到下/从下到上）增加了灵活性
二分搜索大幅提升大数据集查找性能
通配符支持通过匹配模式参数2实现，更加规范
与INDEX组合是最强大的查找组合之一
适用版本：Excel 365和Excel 2021及以上版本

❤️收藏 👍点赞