【原】学习和创新永无止境！一对多查询，各位大神的套路都在这里了。

你好，我是刘卓。欢迎来到我的公号，excel函数解析。在工作和学习中，我们经常需要根据一个条件，来查询出满足该条件的所有值，俗称一对多查询。

关于一对多查询的方法，我相信有些小伙伴已经非常熟悉了，可能还有些小伙伴不太熟悉。其实关于这个问题，各位前辈大神早已开发出了精妙的方法。

今天我继续当个勤劳的“搬运工”，把各位大神的方法和大家分享一下。相信认真学完之后，定有所收获。

-01-

常规用法

下图左表是数据源，现在要根据F3单元格的学历，查出所有的姓名。结果如G列所示。常规的用法是在G3单元格输入下面的公式，按ctrl+shift+enter三键结束，向下填充，直到出现空白单元格为止。

=IFERROR(INDEX(A:A,SMALL(IF(C$3:C$14=F$3,ROW($3:$14)),ROW(A1))),"")

这个公式的思路是：先用if函数判断C列中的每个学历是否等于F3单元格的学历，相等的返回它所在的行号，不相等的返回false。这样就得到一个数组{FALSE;FALSE;FALSE;6;FALSE;FALSE;9;FALSE;FALSE;12;FALSE;FALSE}。

然后用small函数从小到大依次提取出每个行号，再用index函数返回A列中相应行号的内容。

当small函数把所有的行号都提取出来后，公式再向下拖动的时候会产生错误值，用index返回相应内容的时候还是错误值，所以要用iferror来处理下错误值。

基于这个思路还有2种变形的用法，变形1：在H3单元格输入下面的公式，按ctrl+shift+enter三键结束，向下填充。

=INDEX(A:A,SMALL(IF(C$3:C$14=F$3,ROW($3:$14),4^8),ROW(A1)))&""

这个公式和第1个公式的区别在于：当C列的学历不等于F3的学历时，返回一个较大的数字4^8，也就是65536。

这样用small提取行号的时候，还是从小到大依次提取，当所有符合条件的行号被提取完时，公式再下拉时，就会返回65536。

用index返回A列的第65536行的内容时，一般是空单元格，此时会返回0。为了把0不显示，公式后面需要连接空文本“”。

所以这个公式返回的结果是文本，如果你要查询的值是数字或日期时，就会变成文本型的数字。这点是需要注意的。

变形2：思路差不多，公式就不解释了。

-02-

大神套路

大神的套路都是不用按三键，直接下拉填充。

套路1：aggregate

在J3单元格输入下面的公式，向下填充。这个公式的思路其实和常规思路是一样的，只不过aggregate可以忽略错误值来提取最小值。

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($3:$14)/(C$3:C$14=F$3),ROW(A1))),"")

ROW($3:$14)/(C$3:C$14=F$3)这部分返回的结果为{#DIV/0!;#DIV/0!;#DIV/0!;6;#DIV/0!;#DIV/0!;9;#DIV/0!;#DIV/0!;12;#DIV/0!;#DIV/0!}。意思就是当C列的学历等于F3的学历时，返回相应的行号，不等于F3的学历时返回错误值。

套路2：mode.mult

在K3单元格输入下面的公式，向下填充。这个公式用的是mode.mult，它可以返回多个众数，也就是出现频率最高的数。这个公式的用法拆解之前的文章中有分享过。点击《一对多查询又一个新套路》学习回顾。

=IFERROR(INDEX(A:A,INDEX(MODE.MULT((C$3:C$14<>F$3)*{1,2}%+ROW($3:$14)),ROW(A1))),"")

套路3：lookup+多维引用

在L3单元格输入下面的公式，下拉填充。这个公式我也是刚学的，还不是很理解。大家可以自己拆解学习。难理解的点在于错位，对于错位的用法，我个人觉得还是挺别扭的，没有对齐那么好理解。它和下面的套路4其实是类似的。这个公式返回的结果也是文本。

=LOOKUP(ROW(A1)-1,COUNTIF(OFFSET(C$2,,,ROW($1:$20)),F$3),A$3:A$14)&""

套路4：lookup+frequency+多维引用

在M3单元格输入下面的公式，向下填充。此公式的详细用法之前的文章也分享过，点击《一对多查询竟然有这么多方法，惊呆我了！》复习回顾。

=LOOKUP(,0/FREQUENCY(ROW(A1),COUNTIF(OFFSET(C$3,,,ROW($1:$20)),F$3)),A$3:A$14)&""

套路5：利用mmult实现不需按三键

输入下面的公式，向下填充。其实就是在变形2的基础上加了个mmult，实现了不用按三键的效果。

=INDEX(A:A,SMALL(MMULT((C$3:C$14<>F$3)/1%+ROW($3:$14),1),ROW(A1)))&""

今天的分享就到这里，希望对你有所帮助。从今天的分享中，可以看出有一些函数是支持数组运算的，不用按ctrl+shift+enter三键。比如，aggregate，mode.mult，lookup，frequency，mmult，还有sumproduct。

链接：

https://pan.baidu.com/s/1-bhHu6AAVHu3GIdHxLCEfw

提取码：ywuj