一对多查询时的函数组合套路，你一定要牢记！
点击上方
蓝色
文字 关注我们吧！
送人玫瑰，手有余香，请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径！
上周刚和大家讲了一对多查询的的方法。鉴于一对多查询在实际工作中经常出现，今天就再次强调一下一对多查询的函数组合拳！由于VLOOKUP函数那部分需要添加辅助列，相对比较简单，不再详细解释了。
INDEX函数+SMALL函数+IF函数法
完整的公式应该是“=IFERROR(INDEX($B$2:$B$17,SMALL(IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1,100),ROW(A1))),"")”，CTRL+SHIFT+ENTER回车。
思路：
利用IF(A$2:A$17=$J$2,ROW($A$2:$A$17)构造出一个数组{2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;FALSE;FALSE;FALSE;FALSE},再减去1，就得到“天龙八部”这部书所在的行号
IF公式中第三个参数“100”保证了当逻辑值是FALSE是，返回值是100，这个值足够大，至少要大过所有数据的行数，以保证FALSE对应的行不会被INDEX函数抓取到
其余的INDEX函数和IFERROR函数就相对简单了，不再赘述
注意：在IF函数中第三个参数如果不书写，也是可以达到目的的。IF函数部分可以直接写成IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1)也是可以的。
OFFSET函数+SMALL函数+IF函数法
和上面的例子相同，完整的公式为“=IF(ROW()-1>COUNTIF($A$2:$A$17,$J$2),"",OFFSET($B$1,SMALL(IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1,100),ROW(A1)),0))”，CTRL+SHIFT+ENTER回车。
思路：
IF部分的公式和上例是相同的，请参看上例
OFFSET函数利用SMALL函数的返回值进行偏移
着重讲一下ROW()-1>COUNTIF($A$2:$A$17,$J$2)这一部分。它是IF函数逻辑判断的第一个参数，含义是：当当前的行号-1（因为是从第二行开始的）大于某本著作在数据区域中出现的总次数时，意味着所有符合要求的数据都已经被抓取了，就返回空值
同样地，这里的IF函数第三个参数也可以省略。
文章推荐理由：
一对多查询时如此地重要，需要你牢记公式组合套路！
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
推荐阅读：
戳原文，更有料！免费模板文档！