万金油——一对多查询利器

工作中遇到过这样的问题吗？用查找函数查询一些数据，但是会出错。这个错误不是#N/A，不是#VALUE，公式会返回一个值，但是这个值并不是自己想要的结果，公式错了吗？也没错。而且公式已经给你返回了一个值，也不算出错。那问题就在于查找公式的一个逻辑，当它查找到符合条件的第一个值，它就不会再去往下找，直接告诉你，就是它。

例如上面的例子，有两个重名的人，当我用VLOOKUP根据姓名去查找的时候，会发现金额返回都一样。

看到这有人会说，直接多条件查找不就可以了，带上部门一起查找。OK，没问题，可以精准的查找到。

但是今天要说的万金油就是针对一个查找项结果有多个的情况。

经常接触表格和公式的人有可能见过万金油的写法，INDEX+SMALL(LARGE)+IF+ROW。初见万金油，对很多人尤其是萌新来说一脸懵逼外加劝退。但是如果了解过这几个函数分别的用法，相信还是比较容易理解的。下面就说说万金油到底是个啥，它的计算逻辑是什么。

如上面的例子，需要通过年级找到每个年级对应的姓名，如果用VLOOKUP、LOOKUP、INDEX+MATCH这些常见的查找，可以找到对应年级的第一个人，但是无法做向右拖拽或者向下拖拽查到第二个，第三个人的名字。那用INDEX+SMALL(LARGE)+IF+ROW怎么做呢。下面逐步分解一下。

再多啰嗦一句，其实INDEX+SMALL(LARGE)+IF+ROW可以看做是INDEX+MATCH的组合，而MATCH就是被SMALL(LARGE)+IF+ROW所代替。

第一，我们要确定一下要查找的区域或者说要返回值的区域。因为是要找名字，所以就是B列的B1:B15（注意：因为是数组公式，不建议选取B:B整列的样式，如果数据多会严重影响运算速度甚至是卡死，有多少数据就选择多少），这样INDEX的第一参数就确定了；

第二，根据什么去查找。要找名字，名字对应的是年级，我们要根据年级去确定查找方向。所以IF里的内容就可以确定，A1:A15。那既然是个IF函数，肯定会有一个判断方向，所以A1:A5要等于对应的年级。如上面的例子，IF(A1:A15=E1)。

第三，已经有了IF的判断，但是IF判断完，总需要返回一些什么，你总不能告诉IF你就判断一下就得了，判断完你就一边去，没你的事了。这时候IF想着咱也不知道咱也不敢问，索性就告诉你这么一段话，你自己琢磨去吧。

那我们该返回什么，不知道啊。一共就两列，一列名字一列年级，都用上了。这个时候你可以想一下，是不是可以返回区域的序号。把符合IF条件的都抓出来，把不符合的都摘出去扔掉。回头看一眼区域1到15行，利用ROW函数就可以抓到对应的数据。

所以可以告诉IF函数，当你抓到正确的数据，你就给我返回判断符合值的位数。所以在IF里就可以写IF(A1:A15=E1,ROW(1:15))，当公式进行判断时就说这样的 =IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})

最后变成IF({1;0;0;4;0;0;0;0;0;10;0;0;0;0;0})。

到这里，是不是感觉少点什么。SMALL似乎一直没派上用场。没错，这大哥要等IF小弟处理完之前的事情之后，捡现成的。

第四，当IF摘清了要哪个不要哪个之后，我们就需要进行最后一个最重要的步骤，要返回第几个值。是由小到大（SMALL）还是由大到小（LARGE）进行取值。拿上面的例子以SMALL举例。说SMALL拿到仨数，分别是1,4,10。这他nia的咋办，没人告诉SMALL要返回第几个啊，单元格里也没法把这仨都返回。

所以在最后的最后，还需要给SMALL加一个参数。这个参数不固定，可以是用ROW引用行号，也可以用COLUMN引用列号，还可以直接敲数字，这三种用法分开来说。

①ROW引用：当万金油公式需要向下拖拽时，用ROW引用。

②COLUMN引用：当万金油公式需要向右拖拽时，用COLUMN引用。

③数字（辅助列）引用：这种情况很少，同ROW和COLUMN引用原理。

最后，因为不确定每一项可以抓取到几个值，为了整洁美观，不显示#NUM!，可以加个IFERROR函数。完整公式如下：=IFERROR(INDEX($B$1:$B$15,SMALL(IF($A$1:$A$15=$E1,ROW($1:$15)),COLUMN(A1))),'')

注意：ROW（1:15）这个区域是对应B1:B15的区域行数，如果B1:B15区域变为B3:B27,ROW函数的区域可以改为1:25,数组公式三键结束。