vlookup函数的一对多应用

所谓一对多，就是一个查找值对应了多个结果值，如何使用vlookup函数来完成一对多的查找引用，是我们要学习的进阶课题。

一对多查找又有两个常用方法，一个是创建辅助列来提取，另一个则是嵌套其他函数的组合公式引用。

一对多匹配引用是excel表格比较常见的一个场景，但也成为许多使用者的难题。

在过去通常要利用嵌套多个函数的数组公式来达到匹配效果，但随着excel版本更新，新函数filter可以一步到位，快速提取所有符合条件的值。

但今天作者还是以过去应用较多的vlookup函数来介绍具体匹配引用方法。

首先还是继续介绍创建辅助列来一对多查询。

1、创建辅助列

创建辅助列的作用是为了获取一个唯一不重复的查询列表，因此结合原查询列表和countif函数计数值，得到新的查询列。

公式为：=B2&COUNTIF($B$2:B2,B2)

接下来直接设置vlookup函数公式：

=VLOOKUP($E$5&COLUMN(A1),$C:$D,2,0)

其第1参数查找值使用了column函数，它的作用是返回单元格的列序号，比如COLUMN(A1)等于1，COLUMN(B1)则等于2；

第2参数查找区域以辅助列为查询列，然后返回销量列对应的数据。

这个公式的特点，就是通过查找值与column函数的结合，来动态引用查找值对应的多个结果。

如下图所示，由于是横向拖动公式，因此嵌套column函数，将依次得到1、2、3、4的列序号值，而辅助列中查找值与1-4的合并数据，都是唯一不重复的，因而得以被vlookup函数进行动态引用。

既然可以横向引用，自然也可以纵向引用，只需要将column函数替换为引用行序号的row函数即可！

公式为：=VLOOKUP($E$6&ROW(A1),$C:$D,2,0)

从这样一个案例来看，其实vlookup函数的一对多应用非常简单，没有任何复杂的地方，都是基础函数的常见用法。

但接下来要介绍的第二种一对多公式写法，就相对繁琐了，其中嵌套了多个函数，执行数组运算，理解起来需要费些脑细胞。

2、组合公式引用

通过一个公式怎么快速引用所有的结果值，首先要思考解题的思路。

有了创建辅助列解答的经验，其实我们可以借鉴countif函数获取一个唯一值列表。

但countif函数输出的是一个结果，如何使一个结果转换成一个列表，则是问题的关键。

这里作者利用row函数来获取一个数字列表，然后结合indirect函数的单元格引用，来创建一个单元格列表，再利用countif函数计数时，将会得到一个数组结果。

到了这一步，其实已经能够获取不重复的唯一值列表。

但语言描述太笼统，下面直接上公式：

=VLOOKUP($D$5&COLUMN(A1),IF({1,0},$B$2:$B$29&COUNTIF(INDIRECT("b2:b"&ROW($2:$29)),$D$5),$C$2:$C$29),2,0)

这个公式组合了if数组、countif、indirect和row函数，如上所讲，countif函数得到一个计数的数组结果时，然后直接与货号列进行合并，直接得到了与创建辅助列的结果相同的查询列表。

然后利用if数组在vlookup函数中的应用，进行查询列和返回列的设置。从这一步来看，其实之前讲解的vlookup+if函数的两种用法，也是这个案例中的一个解题要点！

最后给vlookup的第1参数查找值连接上column函数，进行横向的动态引用。

对于这个组合公式的理解，可以在公式编辑栏中对公式各组成部分进行分步解析，它的关键点还是在于数组列表的创建和查找区域的指定设置。