Excel函数技巧之返回多个匹配结果

在Excel中进行查找一般都用VLOOKUP和INDEX+MATCH，但是这些函数都只能返回一个匹配结果。比如：

尽管在右边的表中有两行都是可乐类的产品，但是VLOOKUP只返回第一个值。用MATCH也类似。

那么，如果想要把所有匹配的行都返回，应该怎么办？

01

第一种方法 Index+Small

这里，我们需要用到一个比较复杂的公式：

=INDEX($G$3:$G$6,SMALL(IF($F$3:$F$6=B3,ROW($F$3:$F$6)-2,1000),ROW()-2))

这是一个数组公式，需要用CTRL+SHIFT+ENTER输入。

下面简单介绍一下这个公式。

先看最内层的部分：

SMALL(IF($F$3:$F$6=B3,ROW($F$3:$F$6)-2,1000),ROW()-2)

SMALL是一个很简单的函数，就是从一堆数中选择第n小的值，例如SMALL({2，1.5，8}，1）就是返回第一小的值，结果就是1.5。

在我们的公式中，SMALL的第一个参数是一个IF函数，这个函数去判断F列每个单元格bu是否跟B3单元格相等，如果是，就返回F列对应单元格的行号，如果不是，就返回1000（实际上是一个比较大的数，超过可能的最大行号即可），第二个参数就是取第几小的值，因为B3单元格对应的是第一个返回结果，所以取第1小的值：ROW()-2=3-2=1。

这部分公式的计算过程就是:

于是，整个公式就变成了：

INDEX($G$3:$G$6,2)

得到了一个匹配值。

通过拖拽填充即可得到后续的结果：

但是，如果填充超出了我们需要的行数，就会返回错误。此时，我们可以使用IFERROR来进行处理：

=IFERROR(INDEX($G$3:$G$6,SMALL(IF($F$3:$F$6=B3,ROW($F$3:$F$6)-2,1000),ROW()-2)),"")

02

提高公式可读性

这个公式只用了两个函数嵌套在一起：INDEX和SMALL（ROW函数很简单，所有不算）,总体不算复杂。但是可读性还是比较差，不容易一下就看明白这个公式的作用：

我们可以通过定义名称来提高公式的可读性：

再定义G列为产品明细：

于是公式可以写为：

=IFERROR(INDEX(产品明细,SMALL(IF(类别=B3,ROW(类别)-2,1000),ROW()-2)),"")

一个就知道这些参数的含义。

好了，今天的分享就到这里了！