vlookup函数在一对多场景中的使用

vlookup函数是excel表格中非常常用的一个函数，主要用于返回引用查找值在区域中的对应数据，那通常都是一对一的查找匹配。

那么在今天的场景中，我们要通过vlookup函数来进行一对多的查找引用。

比如下方数据表中，已知客户组和各组人员，现在需要使用函数公式来提取客户组A210的所有人员。

一对多查找引用，一直以来都是excel中较为复杂的一类知识，但其实它的解法并不复杂，尤其随着excel版本的更新，也出现了例如FILTER等功能强大的函数，来快速搞定一对多的查询匹配。

但今天我们还是来介绍一下vlookup函数在一对多场景中的使用方法。

首先，我们创建一个辅助列，并在单元格中输入公式：=C2&COUNTIF($C$2:C2,C2)

这个公式的作用，实际上是给客户组添加一个后缀，以使它们变成一个唯一值，比如A2101,A2102…当添加上一个数字作为后缀，则变成了列表中不再重复的一个文本值。

而countif函数在这里的用处很关键，它会返回客户组在指定区域中单元格个数，从而得到一个数字结果，并作为后缀与客户组的文本连接在一起。

这样当我们在使用vlookup函数查询时，也可以设定查找值为"客户组+后缀"的形式。

我们先向下填充公式，得到完整的唯一的查询列数据。

接着我们在单元格中输入vlookup函数公式：VLOOKUP(G4&ROW(A1),$B$2:$D$16,3,0)

公式中第1参数查找值是G4与row函数的结合，row函数的含义是返回单元格地址的行号，其结果也是一个数字，那么G4单元格A210+1（row(a1)的结果），便得到查找值"A2101"。

我们再看第2参数的查询匹配区域，首列是通过公式填充的辅助列，公式会查询第1参数的值在首列中的位置，然后匹配区域中指定列数中的对应单元格。

我们设置第3参数为3，返回引用查询匹配区域中的第3列，即英文名的数据列，也就是说，公式将执行查找A2101在首列中的位置，并返回它在第3列中对应位置的数据，从数据表中可知A2101对应第3列是"Elizabeth"。

但这一步还只查询到客户组A210下的一位人员，我们需要向下填充公式，并组合一个逻辑函数的ifeeror来忽略错误值。

所以完整的公式为：=IFERROR(VLOOKUP($G$4&ROW(A1),$B$2:$D$16,3,0),"")

这里我们需要注意的是，由于要下拉填充公式，因此要记住将第1参数中的G4进行绝对引用；而row函数进行下拉，其结果会自动更新，得到不同数值结果，而使查找值处于不重复的状态中，来查询匹配首列中相同的数据。

最后当G4连接row函数的数值结果无法与辅助列中的数据相匹配，比如G4&row(a5)，结果为A2105,在辅助列中没有数据可以匹配上，因此公式的计算结果会出错，这时iferror函数的作用便体现出来，而返回一个空值，看上去则是一个没有数据的空白单元格。

最后我们来总结一下，一对多查找的关键是灵活设置查找值，通过创建辅助列，得到一个唯一的数据列，然后将它作为首列进行查询匹配。之后再使用row函数来自动更新查找值，从而得到一个关键字下的多个结果。

以上就是今天的全部内容，欢迎关注作者，我们下期再见！

举报/反馈