Vlookup大叔与一对多查找（Excel函数集团）

所谓一对多查找，就是根据一个条件，把多个符合条件的结果全部找出来。

其实吧，一对多查找不是什么，尤其是O365的Filter函数横空出世震撼全场之后，简直就是瞬间把所有传统的数组解法甩出去七八十来条街……

但是！

这世上有一种人，叫“没了Vlookup就吃不好睡不香”，但凡和查找搭上半点关系的问题，都必须肯定一定确定要用早已油腻的Vlookup大叔来解决。

Vlookup大叔表示，心累！

比如表格结构如下图的一个一对多查找问题：

好在V大叔有个特别之处，就是他的参数常常会弄出些意外，比如第一参数￥#￥%@……

再比如第二参数#￥%&￥#……

所以就生出了这么个野路子的公式：

=VLOOKUP(D$2,INDIRECT("a"&SMALL(IF(A$2:A$17=D$2,ROW($2:$17)),ROW(A1))&":b17"),2,)

好吧，这公式就这么一亮相，肯定是支持头晕模式的，还是一步一步来拆解吧。

首先，当我们遇到这个问题时，如果只需要找第一个“乙”所对应的“B”，V大叔的标准写法是：

=VLOOKUP(D$2,A2:B17,2,)

其中的A2:B17是查找范围，直接引用。如果改用间接引用，并不影响公式结果：

=VLOOKUP(D$2,INDIRECT("A2:B17"),2,)

即然用了间接引用，A2:B17就不再是单元格地址，而是变成了字符串，可以各种拆解了，比如拆解成这样：

=VLOOKUP(D$2,INDIRECT("A"&2&":B17"),2,)

这样一来，其中的2就被单独拎了出来，这其中就可以加上一个一对多查找的惯用手法：

=VLOOKUP(D$2,INDIRECT("A"&SMALL(IF(A$2:A$17=D$2,ROW($2:$17)),ROW(A1))&":B17"),2,)

文字解释一下就是如果A2:A17这些等于D2，也就是符合条件时，返回对应的行号，再用Small函数对返回的结果通过公式向下复制的方式从小到大排列。

所以这公式，就是这么回事了。

那么，为什么V大叔这么用就可以起到一对多查找的效果呢？如果把"A"&SMALL(IF(A$2:A$17=D$2,ROW($2:$17)),ROW(A1))&":B17"部分单独放在一个单元格里，前面加上等号，公式完成后再向下复制，就会得出A3:B17、A7:B17、A11:B17和A15:B17这样的结果。

说得再透明点就是，第一个公式是让V大叔在A3:B17这个范围内找D2对应的结果；向下复制到第二个公式是让V大叔在A7:B17这个范围内找D2对应的结果；继续向下复制到第三个公式是让V大叔在A11:B17这个范围内找D2对应的结果；第四个公式是让V大叔在A15:B17这个范围内找D2对应的结果。

所以这结果是正确的、愉快的、大得V大叔忠粉们心的、皆大欢喜的……

可偏偏这公式，是个野路子，因为，选取第二参数后按F9功能键，结果是奇特奇异奇怪的。

当然这结果也并非来自地球以外，之所以会这样，是因为Indirect的结果，是多维引用，而正经路子多维引用都是在外面套用SumIf、Subtotal、DSum这些函数，且完成以后的公式还可以再在外面套函数，详参多工作表汇总的相关公式。

而V大叔用在这时，就只能这样了，想要在外面再套别的函数，那就……没结果了。

这是不是函数中的一个Bug呢？

但是，不管是真Bug还是假Bug，不管是正路子还是野路子，只要能解决问题，同时大得V大叔忠粉们的心，就是好路子！

最后提示一小下下，如果您的电脑不支持动态数组，公式结束后请按【Ctrl+Shift+Enter】组合键，以免乐极生悲。

巴特西

Vlookup大叔与一对多查找（Excel函数集团）

最新文章

热门文章