Excel一对多万能查询公式index small if，理清思路就会了！

工作中vlookup函数更多用于一对一的查询，如果碰到了一对多的情况，经常会看到使用index+small+if函数公式

例如左右是基础信息，然后我们列出一个部门，需要把部门成员全部找出来，则可以使用这个一对多的万金没公式：

=INDEX(A:A,SMALL(IF($C$1:$C$6=$F$2,ROW($C$1:$C$6),2^10),ROW(A1)))&''

输入完公式按CTRL+SHIFT+ENTER三键进行计算

很多文章中都出现了这个公式，但很少有解释为什么这么用的，我们来进行思路理解

❶首先我们查找的是市场部，所以首先组成一个数组，得到所有是市场部的列序号

使用公式一：$C$1:$C$6=$F$2，得到的结果是：

❷然后我们要保留得到TRUE的结果，FALSE的结果让它变得无限大，我们这里使用的2的10次方，也就是1024，当然可以修改的更大，比如2的100次方

使用IF(公式一,row($C$1:$C$6),2)

❸然后使用SMALL函数，找到第1小的数字，第2小的数字，第3小的数字....以此类推

❹最后用Index函数，根据这些得到的索引序列号2，4，6，我们要得到的结果是姓名的话，也就是找到A列的第2个，第4个，第6个...就是我们的结果：

A列的第1024个数字为空，所以显示成了0

❺最后用&''，让这些数字0不显示出来，就得到了最后我们需要的结果：

所以这个万金油公式，你理解了么？

如果我们用INDEX+LARGE+IF也可以进行求解：

=IFERROR(INDEX(A:A,LARGE(IF($C$1:$C$6=$A$10,ROW($C$1:$C$6),-1000),ROW(A1))),'')

它的求解是从下向上的求出所有结果。

所以你学会了么？欢迎留言讨论~

专栏

Excel公式函数技巧视频教程