XLOOKUP和XMATCH 的使用方法

小伙伴们好啊,我又来了。

近日由于Excel新出的X系列函数,XLOOKUP和XMATCH,在论坛和QQ群都引起很大反响,因为这2个函数的出现简化了之前一些查找公式的难度,造福新手。

然而,哪怕出现了这2个函数,还是有一个查找函数能与之比肩,就是MATCH 。

因为强大的LOOKUP本质只有一种查找方式(即目前习惯称为二分法的默认升序下的查找方式)，VLOOKUP有2种，二分法和遍历法(后者在查找区域里从上往下挨个找,找到对应内容即终止,这也是常规VLOOKUP查找时结果为对应第1个值的原因),用4参指定查找方式。

然而如果熟悉MATCH的话你会发现,除了最新的的XLOOKUP和XMATCH出现所谓降序排序的查找外,早期版本只有MATCH具备这个查找方式。

也就是说,目前已知的3种查找方式,除了X系列的2个新函数,MATCH也同时具备,这也成就了这个函数在一些特殊方面的应用。

下面就是MATCH的一些用法举例。

1 常规查找

VLOOKUP/INDEX和OFFSET等配合使用。

例如上面的VLOOKUP的多列查找问题,核心在自动获取对应列数。这是MATCH的强项。

因为MATCH的主要用途就是帮你数数。

2 通配符查找

当MATCH的3参为0时,上面已经提过使用的是遍历法的查找方式,这种查找方式下支持通配符。

所以可以使用MATCH处理一些涉及包含关系字眼的查找。

?指代任意1个字符, *指代任意多个字符(0-255范围)。

3 升序查找方式

缺省3参数或者3参数为1时的查找方式为升序查找，微软更新XLOOKUP的重要原因之一就是很多人没有弄清所谓升序排列时的模糊查找的原理。

模糊查找这个词坑了不少新手,这种查找效果更接近范围取值效果(2参升序时),例如这个常见的日期转季度问题。

4 “1,0/” 结构的用法

案例3是二分法的正常使用方式,然而这个查找方式早就被前辈们挖掘透彻创造出了 1,0/ 结构的用法。

这个结构下的结果为最后1个对应内容(注意此时3参为1或者直接缺省)。

(新出的XLOOKUP和XMATCH貌似有找最后1个的效果,但二分法的 1,0/ 结果下一样能实现)。

注意数组公式需要使用CTRL+SHIFT+ENTER三键完成,下同。

5 涉及数组

涉及数组后用MATCH多条件查找也是个常见问题。

6 字符提取

由于MATCH的2参数支持数组,因此在字符提取等很多需要获取对应位置的相关问题里经常有MATCH的身影。

7 去重问题

介绍到这里必须提MATCH在去重类问题的重要意义,由于在3参为0时找对应第1个的性质,闯出了MATCH在去重问题的一番作为。

8 指定次数重复

按指定次数重复也能用MATCH试试。而且上述的2种查找方式都能实现。 

10 加权排名

MATCH支持单列数组并返回位置的特性成就了这个函数在一些加权排名里的作为。

11 数学计算

如果能看懂案例9,那么你会发现MATCH是可以用来算一些数学题的。

(这里的公式是为了举例故未简化,数学好的童鞋肯定发现了这个解法下部分参数可以简化,譬如4个4个拿剩1个则2个2个拿必然剩1个)。

11个案例,介绍了下MATCH的一些常见和不常见的套路,然而如果你以为到这里就结束了,那就大错特错了。

之所以介绍MATCH，是为了介绍3参数为-1时的特殊查找方式,这种查找在论坛主流认为并不是降序方式的二分法而是一种特殊遍历查找方式。

常规用法当然是降序时使用。

降序下MATCH的查找效果。

但是在乱序状态下,这个查找方式在一些特殊环境下有奇效!

MATCH在合并单元格与类似结构下的特殊应用：

12 分摊奖金

这是群里一个经典题,难点在于不使用辅助列时定位最后1组合并单元格的个数。

因为很多公式不能直接解决最后1组合并单元格的单元格个数,要单独处理。而MATCH -1用法在这里成为经典。这是目前MATCH -1的一个经典应用。

由于在XMATCH与XLOOKUP前 降序下的查找是MACTH的专利,所以说这才是X系列前最强的查找函数。