【原】Excel中比MATCH函数好用得多的XMATCH函数用法

  文 前 推 荐   

MATCH函数用法

LOOKUP二分法

异或函数XOR用法

可区分字母大小写的查找方法

编按：

新函数XMATCH的用法。通过它与老函数MATCH的对比，可以发现它不需要排序、默认完全匹配，公式书写简单，因此，如果你用的是高版本Excel或者新的WPS版本，则就用XMATCH取代MATCH吧。

新函数XMATCH是老函数MATCH的全面升级版，Excel2021及以上，或者WPS表格的新版本都支持。

1.  作用与语法

在数组或者区域中查找指定数据并返回其相对位置。

=XMATCH(查找值,查找区域,[匹配方式],[查找方式])

第一、二参数必须有，作用与MATCH函数相同。

第三参数，匹配方式，相比MATCH变化大，有4个选项，具体如下。可以看出，XMATCH最大的优势就是不需要排序。

第四参数，查找方式，有4个选项，若非必要，一般省略第4参数。MATCH函数没有第四参数。

2.  基本用法

1）完全匹配——查找等于查找值的值的位置

譬如查找等于100的数据的位置。用XMATCH很简洁，因为默认就是完全匹配，所以用前方两个参数即可。而用MATCH函数，必须加上第3参数0。

2）查找小于等于查找值的最大值的位置

譬如查找小于等于101的最大值的位置。由于当前是降序排列，所以MATCH查找结果是错误的，而XMATCH采用遍历法没有排序要求，结果是正确的。

3)查找大于等于查找值的最小值的位置

譬如查找大于等于98的最小值的位置。由于当前A2:A10是降序排列，所以MATCH的查找结果与XMATCH的查找结果一致，都是正确的。

4）使用通配符模糊匹配

譬如查找包含101的数字的位置。XMATCH和MATCH都支持使用通配符进行模糊查找，使用的区别在于，XMATCH有专门的匹配选项数字“2”，而MATCH需要沿用完全匹配选项“0”。

说明：

由于当前A2:A10是数字，所以要查找包含字符101的数据需要将其转化为文本。公式中的TEXT函数作用就是将A2:A10转化为文本。

5）逆序查找：查找最后一个符合要求的数据的位置

XMATCH默认是从开始到结尾查找第一个符合要求的数据的位置。如果要查找最后一个，则需要逆序查找，第四参数写为-1。

6）二分法查找：排序后查找

XMATCH默认都是采用遍历法查找的，不需要排序。但如果强迫XMATCH采用二分法查找，就必须排序，然后设置第四参数为2或者-2。

譬如查找大于等于101的最小值的位置，公式=XMATCH(D46,A46:A54,1,-2)

说明：

查找大于等于查找值的最小值，意味第三参数是1；当前数据A46:A54是降序排列，用二分法查找，第四参数就必须是-2。

如果此处A46:A54是升序排列，第4参数就必须是2。

3.  典型应用

XMATCH与MATCH类似，通常与其他函数搭配使用，最常见的就是与INDEX函数搭配。

1）常规查找

譬如查找“花无缺”的数学成绩。

=INDEX(D2:D7,XMATCH(A11,A2:A7))

2）交叉查找

譬如查找花无缺的数学、铁心兰的英语成绩。

=INDEX($C$14:$E$19,XMATCH(A23,$A$14:$A$19),XMATCH(B23,$C$13:$E$13))

说明：

第一个XMATCH函数确定返回的行数，第二个XMATCH函数确定返回的列数。

3）查找等级

如果用下限值作为查找区域，就是在各等级的下限值中查找小于等于查找值的最大下限所对应的等级。譬如查各学员的成绩等级。

=INDEX($G$27:$G$31,XMATCH(C27,{135,120,105,90,0},-1))

如果用上限制作为查找区域，就是在各等级的上限值中查找大于等于查找值的最小上限所对应的等级。譬如查各学员的成绩等级。

=INDEX($G$27:$G$31,XMATCH(C27,{150,134,119,104,89},1))

4）查找最接近的数

譬如下方，查找最接近70的数。

=INDEX(A35:A41,XMATCH(0,(70-A35:A41)^2,1))

说明：

最接近的数，意味着原数据与它之间的差值的绝对值最小或者差值的平方最小。

公式中XMATCH(0,(70-A35:A41)^2,1)表示求大于等于零的最小差值平方数的位置。

如果用绝对值，公式=INDEX(A35:A41,XMATCH(0,ABS(70-A35:A41),1))

课件下载方式

扫码入群，下载本文教程配套的练习文件。

---

最后，分享柳之老师刚开发的《Excel人事管理模板课程》，预览如下：

宠 粉 福 利

 2元领取：全套Excel技巧视频+200套模板