谁说VLOOKUP函数不能一对多查找？

本文是专门为新书打造的「拓展阅读系列」之一，我们希望做到实体书与线上学习的有效结合，通过二维码打通实体书与线上资源，构建完善的Excel知识体系。

我以前写过两篇关于VLOOKUP函数的文章，流传甚广，在这两篇文章中，我把VLOOKUP吹上了天。

如果你对此函数用的不太熟，先来学习这两篇文章：

入门|快速掌握VLOOKUP函数之精解精析

进阶|熟练使用VLOOKUP函数之精解精析【深度长文】

在众多查找函数中，VLOOKUP确实称得上扛把子的，但是它也有局限性，这不，一对多查找他似乎就无能为力。

检察院以人民的名义派了三个小组去抓贪官，需要从左面的表格中查找出二组的成员，但是二组对应两个成员，也就是说一次要查找并返回两个值。

我们知道Vlookup总会以第一个被找到数据作为最终的匹配数据，所以，当有两个”二组“同时存在时，很自然的，它只能默认为匹配首个，也就是说只能返回”高育良“。

那么，是不是传说中的Vlookup遇到这种情况也只能望洋兴叹，无能为力啊？

当然不是！

作为专业从事匹配工作的函数，Vlookup只是需要一些额外的辅助。

需要注意的是，原生态的Vlookup，用于匹配的数据必须是唯一的，这是由函数的四个参数决定的，改变不了这个事实，所以我们只能从查找匹配的数据源上进行改造。

虽然一个组别对应多为成员，但是这种对应也是有规律的。比如，第一个“一组”对应“侯亮平”，第二个“一组”对应“陈海”……

所以，为了实现一对一匹配，需要构建出组别的次序。在C2单元格中输入公式=COUNTIF(A$2:A2,A2)，并向下复制填充，可以得出每一个组别的次序。

然后在B列左侧插入一个新列，并将A列数据和C列数据组合（使用&进行单元格组合），形成新的数据列，如图。

奇迹出现了，使用新组别这一列数据去查找成员，相当于为每一个组别创造了唯一的识别码，再用Vlookup时，就能精确地一对一匹配到了。

你看，原以为搞不定一对多排序是VLOOKUP函数的问题，实际是数据源的问题。至此，可得出解决此问题的关键点为：

①通过COUNTIF函数，制造出序列（难点是A$2:A2动态引用，这是创造正确编号的核心）；

②通过&组合组别和次序，制造唯一性。

万事俱备只欠东风，最后一步——Vlookup多条件匹配。

在G2单元格中写入公式=VLOOKUP(F$2&ROW(A1),B:C,2,0)，然后向下复制填充，直到出现#N/A错误，则会返回“二组”对应的所有成员。

这里使用ROW(A1)函数生成序列，然后再与F2单元格组合，于是就依次生成“二组1”，“二组2”，相当于VLOOKUP函数的第一个参数依次按照“新组别”中的参数出现。

上面的查找方式，将查找的多个结果依次放入不同的行单元格中，这就导致无法批量查找，即无法同时查找“二组”和“三组”的成员，必须分开写公式。

所以，通常情况下，我们会将查找到的多个结果放入不同列中，效果如下图所示。

稍微对公式进行修改，就能实现这样的效果。

在G2单元格中写入公式=VLOOKUP($F2&COLUMN(A$1),$B:$C,2,0)，并向下拖动填充，然后向右拖动填充，直到每一个组别对应的成员都出现错误值#N/A为止，这说明每一个组别对应的成员都被查找出来了。

我们将此公式

=VLOOKUP($F2&COLUMN(A$1),$B:$C,2,0)

与上文中的公式对比

=VLOOKUP(F$2&ROW(A1),B:C,2,0)

首先第一个参数变成了($F2&COLUMN(A$1)，这是因为我们需要同时查找“二组”、“三组”、“一组“对应的成员，因此在向下复制时，引用的单元格需要依次变更为F3、F4，所以这里的$F2需要对行需要对行进行相对引用，又因为公式需要向右复制，必须确保引用的一直是F列的数据，所以列为绝对引用。

同理，我们在列方向进行填充，因此需要是使用COLUMN函数构造序号。

因为我们事先不知道一个组别对应几名成员，所以必须一直向右填充公式，直到出现错误值为止，才算把所有的数值查找完毕。

为了避免出现错误值，可以使用IFERROR函数进行嵌套。

将G2单元格中的公式改为

=IFERROR（VLOOKUP($F2&COLUMN(A$1),$B:$C,2,0)，'')

第一参数就是查找公式，第二参数代表“如果第一参数的运算结果为错误值时所显的自定义的值”。这里，可以设定为''，也就是显示为“空”。

这样，向右复制到出现空单元格为止即可。

卧槽，一个VLOOKUP函数都被玩出花了！

·END·