Vlookup大叔与一对多查找(Excel函数集团)

发布于 2022年 01月 12日 09:08

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

其实吧,一对多查找不是什么,尤其是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】组合键,以免乐极生悲。

推荐文章