”万金油“公式分步详解

前言

INDEX SMALL IF ROW函数组合，即所谓的”万金油公式“，其作用为一对多查询。之所以叫“万金油”公式，是因为这个公式可以灵活多变，适应各种查询。请见下图：

这是一个数组公式，输入完公式后要CTRL SHIFT 回车结束。下面我们来分解。

1

结构分析

=INDEX(B:B,SMALL(IF(A$1:A$6=D$1,ROW($1:$6),4^8),ROW(A1)))&''

这个公式中一共包含4个函数，把结构简化一下就是：

＝INDEX ( B:B , SMALL ( IF() , ROW() )）

2

拆解

（1）SMALL作为INDEX函数的第二参数，作用是返回INDEX第一参数区域中的行序号，即张三所在的行。

（2）IF为SMALL函数的第一参数，作用是返回一个由行序号构成的数组。

（3）ROW（A1）作为SMALL的第二参数，作用是下拉时返回1、2、3......这样的序号。

（4）ROW($1:$6)作为IF函数的第二参数，作用是在IF第一参数为真的情况下返回1－6的数字。

IF的第二参数4^8是4的8次方，即65536，这里可以理解为返回一个足够大的数字。整个公式由内向外用文字描述是这样的：

第一步：当A$1:A$6中的值=D$1即张三时，返回和该值对应的行号，否则返回65536。这样就形成了一个数组：

第二步：用SMALL函数，下拉时分别取出上面数组中第1小、第2小、第3小....的值。

第三步：用INDEX函数返回B列中，由SMALL返回的行序号对应的值，即张三对应的数字。后面之所以要&''，目的是在下拉公式行数多出结果数的时候，返回一个空白而不是0，以达到美观的目的。这样做有一个bug，就是结果会变成文本，如果后期用于计算，可以把&''去掉。

3

公式使用注意事项

第一、这是一个数组公式，所以要CTRL SHIFT 回车结束；

第二、因为需要下拉，所以公式中的区域引用要用$锁定。