上面的操作时一个典型的一对多查找，查找条件是部门，在数据源内每个部门对应的都是多个数据，万金油公式最主要的用途就是用来解决一对多查找等一些相对复杂的问题。上面动画中的公式为：

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")

发际线后移怎么恢复（发际线后移怎么恢复男生）

上面这个公式一共用了六个函数：IFERROR、INDEX、SMALL、IF、ROW和MATCH，其中的IFERROR和MATCH是本例中辅助性的两个函数，其余的四个INDEX-SMALL-IF-ROW是完成此操作的核心函数。

因此我们先来学习这个核心部分的原理：

F4单元格的公式为：

=INDEX($A$2:$A$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)))

INDEX公式：INDEX函数基本功能是给出一个区域，然后根据对应的行列位置返回查找结果，上图中INDEX查找的数据区域就是姓名所在的区域$A$2:$A$21。

INDEX函数的基本结构是：INDEX（查找区域，第几行，第几列），如果区域是单行或者单列的话，后面两个参数可以省略一个。

在上面这个例子里，区域是在一列，所以我们只需要确定每个数据在第几行就行。

剩下的函数SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))

注意上图内容，销售部一共有四条记录，分别在数据区域的第6、9、10和16行（以表格中的数字行为标准）。

因此我们希望公式下拉的时候，INDEX的第二个参数分别是6、9、10和17这四个数字（

SMALL函数的基本结构：SMALL（一组数，第几小的数）

第一个参数比较好理解，第二个参数主要是判断一组数中的数据比该数字略大的一个数字

如果想要下拉来实现参数变化的话，第二参数就需要用到ROW函数，也就是这样修改：

ROW函数的含义很简单，得到的就是参数的行号，通过这个公式，就可以实现将A列的数据从小到大排序。

为了得到第6，9,10,17行的内容，需要用SMALL函数依次得到这四个数字，思路是通过判断C列是否与F2一致，如果一样得到行号，如果不一样，就得到一个比最大行号还大的数字（目的是为了防止被查找到）：

要实现这个目的，就需要使用IF函数进行判断

IF($C$2:$C$21=$F$2,ROW($1:$20),99)，用这一段来作为SMALL的第一个参数。

关于这段IF，就比较容易理解了，我们可以借助F9来看看这段公式的结果：

因为我们的数据就20个，所以IF的第三个参数使用99就足够了，如果数据量比较大的话，可以用9^9，表示9的9次方，反正足够大就行。

搞清楚这个IF的话，再来看这段

SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))是不是就没那么晕了。

关于SMALL这部分，一定要明白是随着公式下拉的时候，逐个得到我们希望得到的那几个数字，然后用这些数字作为INDEX的第二参数，就可以得到最终需要的结果。

万金油的核心就是INDEX、SMALL、IF和ROW，请大家务必反复琢磨，把这部分原理搞清楚。

注意：数组公式，因此需要我们按Ctrl+Shift+回车得到计算结果。

至于一开始的公式，考虑到要查找多列的内容，所以INDEX的数据区域用的$A$2:$D$21。多列的时候，就需要提供列位置才能找到目标值，因此用MATCH(F$3,$A$1:$D$1,0)来确定数据在第几列。

每个部门的数据都不一样多，我们需要将公式多向下拉几行，这时候就会产生一些错误值，在公式的最外层使用IFERROR函数屏蔽了错误值，使得查询结果看起来非常干净。