用INDEX SMALL IF函数组合实现“一对多查询”，就是这么简单！

我们先来看下两种借助辅助列进行“一对多查询”的方法，一种是用VLOOKUP函数，一种是用INDEX MATCH函数组合。

用VLOOKUP函数实现“一对多查询”

如下图所示，首先在A列建立辅助列，在A2单元格输入如下公式，然后向下填充至A10单元格。

=(B2=$H$2) N(A1)

这个公式是用来统计H2单元格的内容第几次出现在B列，返回1、2、3...n序列值。

图1

然后在I2单元格输入如下公式，然后向右、再向下填充至L10单元格。

=IFERROR(VLOOKUP(ROW(1:1),$A:$F,COLUMN(C:C),0),'')

图2

用INDEX MATCH函数组合实现“一对多查询”

用INDEX MATCH进行“一对多查询”的原理和VLOOKUP相同，都是将对班级信息的查询，转换成对数字序列1、2、3....n的查询。因此只需要将图2中的公式改为如下形式，即可完成查询。

=IFERROR(INDEX(C:C,MATCH(ROW(1:1),$A:$A,0)),'')

图3

以上两种方法的详细讲解及注意事项，可以参考之前的一篇文章：Excel中两种常见的“一对多”查询方式横向对比，孰优孰劣？

用INDEX SMALL IF函数组合实现“一对多查询”

这种方法的最大好处是不需要辅助列，直接输入公式即可得到结果，比前两种方法更加便捷！如图4所示，我们在H2单元格输入如下公式，然后按住Ctrl Shift Enter，然后向右、向下填充至K10单元格。

=IFERROR(INDEX(B:B,SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1))),'')

最终的查找结果如下图所示：

图4

我们来一步步分析这个公式的运行原理。

IF($A$1:$A$10=$G$2,ROW($1:$10))

这部分公式用来将A1:A10范围内的值与G2单元格的值进行逐个比对，如果相同，则返回A1:A10对应的行号（ROW函数的作用）。我们可以选中这部分公式，然后按下F9键查看最终结果。

{FALSE;2;3;FALSE;5;6;FALSE;FALSE;FALSE;FALSE}

发现返回的数组中只有2、3、5、6四个值，其它均为FALSE，也就是说在A1:A10范围的第2、3、5、6行找到了需要查找的数据！

SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1))

SMALL函数对上一步得到的数据结果进行取数，随着公式向下填充，依次提取第1、2、3...n个最小值，这些数字对应的是符合条件班级的行号。比如我们选中H2单元格中的这部分公式，按F9显示查找结果{2}，即得到了第一个匹配结果的行号为2。

INDEX(B:B,SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1)))

这一步，是用INDEX定位对应B列数据的位置（第2行），结果为B2。

最后的IFERROR函数是为了屏蔽多余行中的错误。因为，如果没有IFERROR函数，查询结果是这个样子的：

图5

总结

本文通过讨论了三种“一对多查询”的方法，带领大家进一步熟悉了几个函数组合在查询中的典型用法。我们不难看出三种方法各有优劣，在实际的工作实践中到底选择哪一种方法，要根据它们的特点，以及任务量大小等综合考量。

三种方法的优缺点对比：

1. VLOOKUP函数

优点：公式相对简单，容易理解。缺点：需要借助辅助列，并要求辅助列必须是查询范围的第1列。

2. INDEX MATCH函数组合

优点：灵活多变，不用考虑辅助列顺序。缺点：需要借助辅助列，不太方便。

3. INDEX SMALL IF函数组合

优点：非常方便，不需要辅助列，一个公式就可以解决问题。缺点：公式相对复杂，不易理解。