【原】MATCH函数，XMATCH函数

ExcelEasy 2022-03-23

展开全文

MATCH函数是比较常用的函数。与VLOOKUP等函数作用类似，都是根据给定条件进行查找的。区别是VLOOKUP函数可以返回需要的值，而MATCH函数则返回匹配值的索引号。

MATCH

MATCH函数的作用是根据查找值在查找区域中进行匹配，如果匹配成功，并返回匹配项在查找区域中的索引号；如果匹配成功多项，返回第一个匹配项的索引号；如果匹配不成功，返回错误值#N/A。

语法如下：

MATCH（查找值，查找区域，匹配模式）

其中，

查找值 => 是用来进行匹配的条件。可以是任意数据类型。
查找区域 => 需要对查找值进行搜索匹配的区域。
匹配模式 => 指示MATCH函数如何进行匹配，有三个值：

0，精确匹配
1，近似匹配，返回小于或者等于查找值的最大值。这也是匹配模式这个参数的缺省值。如果匹配模式设定为1或者缺省，查找区域必须升序排列。
-1，近似匹配，返回大于或者等于查找值的最小值。如果匹配模式设定为-1，查找区域必须降序排列。

这里有两个要注意的地方：

MATCH函数的第二个参数：查找区域只能是单列或者单行区域。如果是多行多列区域，函数会报错。
与VLOOKUP函数相似，MATCH函数也支持精确匹配和近似匹配。不过MATCH函数的近似匹配多了一种方式：返回大于或者等于查找值的最小值。

MATCH函数的示例

下面是MATCH函数的一些示例。

示例1：精确匹配

当匹配模式参数为0时，MATCH使用精确匹配：

=MATCH(E2,B3:B11,0)

下面是结果：

精确匹配时不要求查找区域的顺序。

示例2：近似匹配

当匹配模式参数是1时，采用近似匹配。返回小于等于查找值的最大值：

=MATCH(E2,B3:B11,1)

返回结果如下：

在这个场景下，查找区域必须是升序排列。

当匹配模式参数是-1时，采用近似匹配。返回大于或等于查找值的最小值：

=MATCH(E2,B3:B11,-1)

结果如下：

示例3：返回某个值的开始和结束索引号

这是一个比较经典的应用，我们有时需要在一列数据中，找到起始位置和结束位置，这个需求可以通过MATCH函数来解决：

这里有一个条件：需要将查找区域升序排列。（这是自然满足的，因为我们要找某个值的起始和结束位置）

然后通过精确匹配：

=MATCH(E2,B2:B11,0)

找到起始位置。

通过近似匹配：

=MATCH(E2,B2:B12,1)

找到结束位置。

示例4：MATCH函数和INDEX函数配合

这也是经典的应用场景，用来解决“反向查找问题：

首先通过MATCH公式根据数量找到在C3:C11中的索引号，然后根据索引号用INDEX函数在B3:B11中返回正确的类型。

关于INDEX函数用法，请参见这篇文章。

示例5：大小写敏感匹配

MATCH函数是大小写不敏感的。但是我们可以通过MATCH函数和EXACT函数结合，进行大小写敏感的查找：

对这个公式的理解需要了解EXACT函数和数组公式。

示例6：支持数组

MATCH函数支持在数组中进行查找：

=MATCH(120,{100,110,120,130,140,150},0)

这个公式返回3。

XMATCH

XMATCH函数是Excel中的新函数，在Office 365中的Excel以及网页版Excel和Excel 2021中可以使用。

这个函数可以看作是MATCH函数的升级版函数，完全覆盖MATCH函数的功能，并且更为强大。

XMATCH函数和MATCH函数的关系，正如VLOOKUP函数和XLOOKUP函数的关系，升级的方向都一样。

XMATCH函数的语法如下：

XMATCH（查找值，查找区域，匹配模式，搜索模式）

其中，

查找值 => 是用来进行匹配的条件。可以是任意数据类型。
查找区域 => 需要对查找值进行搜索匹配的区域。
匹配模式 => 这个参数定义了XMATCH使用的匹配类型，一共有四种：

0 - 精确匹配。
如果没有找到，就返回#N/A，或者返回[匹配不成功时的返回值]（如果已经定义）。
-1 - 精确匹配，或者下一个较小的项。
这种方式其实就是MATCH函数的匹配模式为1时的匹配方式。在所有值中，找到所有小于等于查找值的的数据，然后找其中最大的一个。
1 - 精确匹配，或者下一个较大的项。
这种方式跟上一种相反：在所有值中，找到所有大于等于查找值的数据，然后找其中最小的一个。这是MATCH函数中的匹配模式为-1时的匹配方式
2 - 通配符匹配。
如果这个参数被设置为2，表示查找值中含有通配符：*,?,~都有特殊含义。