excel一对多查找函数(excel如何一对多查找)

导语：Excel中最典型的一对多查找公式，堪称Excel中的“万金油公式”

查找一个姓名对应的多个销售额，公式该怎么写？

今天跟大家分享一个工作中的经典问题：一对多查找。今天跟大家分享的公式也是非常经典的公式。

下图中，根据F3单元格的姓名，在B2:D14这个单元格区域中查找该姓名对应的所有销售额，并显示在G列对应的单元格中。

具体操作步骤如下：

1、选择G3单元格，在编辑栏中输入公式：=INDEX(D:D,SMALL(IF(C$3:C$14=F$3,ROW(C$3:C$14),ROWS(D:D)),ROW(C1)))&，按组合键“Ctrl + Shift + Enter”结束数组公式的输入，此时公式左右两边会自动加上大括号。

2、将G3单元格的公式下拉填充至G6单元格即可。

3、公式解析。

（1）IF(C$3:C$14=F$3,ROW(C$3:C$14),ROWS(D:D))：

如果C$3:C$14这个单元格区域的内容与F$3这个单元格的相等，就放回C$3:C$14这个单元格区域所在的行，否则返回整个工作表的行数。该公式返回的结果是：{1048576;4;1048576;1048576;7;1048576;1048576;10;1048576;1048576;13;1048576}，我们可以看到，如果C列的姓名等于“刘子琪”的，那么公式返回的结果都是姓名所在行的行号，否则返回的是整个工作表的行数，整个工作表的行数是1048576行。

（2）SMALL(IF(C$3:C$14=F$3,ROW(C$3:C$14),ROWS(D:D)),ROW(C1))：

由第一步公式得出的结果，代如上面的公式：SMALL({1048576;4;1048576;1048576;7;1048576;1048576;10;1048576;1048576;13;1048576},ROW(C1))，SMALL函数的功能是：返回数据组中第K个最小值，ROW(C1)是得到C1单元格所在的行号，为1，当公式下拉的时候，C2会依次变成C2、C3...，行号也会依次加1，所以该公式的意思是：在这个数据组{1048576;4;1048576;1048576;7;1048576;1048576;10;1048576;1048576;13;1048576}中，查找第1、2、3、4小的值，也就是4、7、10、13。

（3）=INDEX(D:D,SMALL(IF(C$3:C$14=F$3,ROW(C$3:C$14),ROWS(D:D)),ROW(C1)))：

INDEX函数的功能是：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。由第（2）步可知，查找到的值为4、7、10、13，所以该公式相当于在D列销售额中，返回第7行、第7行、第10行、第13行的值，也就是2940、2680、2750、2685。

（4）=INDEX(D:D,SMALL(IF(C$3:C$14=F$3,ROW(C$3:C$14),ROWS(D:D)),ROW(C1)))&：

公式后面使用连接符(&)与空字符串()连接，有2个作用：当返回的数据是数值型时，加上&，变成了文本型数据；当返回是空单元格数据时，比如第（3）步，公式=INDEX(D:D,1048576)，返回的结果是0，加上&，就能把0屏蔽掉，显示为空。

4、完整的动图演示如下。

好了，今天就跟大家分享到这里，如果文章对您有帮助，可以转发分享给更多的朋友，创作不易，希望多多支持，谢谢！

免责声明:本站部份内容由优秀作者和原创用户编辑投稿，本站仅提供存储服务，不拥有所有权,不承担法律责任。若涉嫌侵权/违法的，请与我联系，一经查实立刻删除内容。本文内容由快快网络小森创作整理编辑！