excel中用vlookup自动进行一对多查找函数

vlookup函数是excel表格中非常常用的一个函数,主要用于返回引用查找值在区域中的对应数据,那通常都是一对一的查找匹配 。
那么在今天的场景中,我们要通过vlookup函数来进行一对多的查找引用 。
【excel中用vlookup自动进行一对多查找函数】比如下方数据表中,已知客户组和各组人员,现在需要使用函数公式来提取客户组A210的所有人员 。

excel中用vlookup自动进行一对多查找函数

文章插图
一对多查找引用,一直以来都是excel中较为复杂的一类知识 , 但其实它的解法并不复杂,尤其随着excel版本的更新,也出现了例如FILTER等功能强大的函数 , 来快速搞定一对多的查询匹配 。
但今天我们还是来介绍一下vlookup函数在一对多场景中的使用方法 。
首先,我们创建一个辅助列,并在单元格中输入公式:=C2&COUNTIF($C$2:C2,C2)
excel中用vlookup自动进行一对多查找函数

文章插图
这个公式的作用,实际上是给客户组添加一个后缀,以使它们变成一个唯一值,比如A2101,A2102…当添加上一个数字作为后缀,则变成了列表中不再重复的一个文本值 。
而countif函数在这里的用处很关键,它会返回客户组在指定区域中单元格个数,从而得到一个数字结果,并作为后缀与客户组的文本连接在一起 。
这样当我们在使用vlookup函数查询时,也可以设定查找值为"客户组+后缀"的形式 。
我们先向下填充公式,得到完整的唯一的查询列数据 。
excel中用vlookup自动进行一对多查找函数

文章插图
接着我们在单元格中输入vlookup函数公式:VLOOKUP(G4&ROW(A1),$B$2:$D$16,3,0)
excel中用vlookup自动进行一对多查找函数

文章插图
公式中第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),"")
excel中用vlookup自动进行一对多查找函数

文章插图
这里我们需要注意的是,由于要下拉填充公式 , 因此要记住将第1参数中的G4进行绝对引用;而row函数进行下拉,其结果会自动更新,得到不同数值结果 , 而使查找值处于不重复的状态中,来查询匹配首列中相同的数据 。
最后当G4连接row函数的数值结果无法与辅助列中的数据相匹配,比如G4&row(a5) , 结果为A2105,在辅助列中没有数据可以匹配上,因此公式的计算结果会出错,这时iferror函数的作用便体现出来 , 而返回一个空值,看上去则是一个没有数据的空白单元格 。
最后我们来总结一下,一对多查找的关键是灵活设置查找值 , 通过创建辅助列,得到一个唯一的数据列,然后将它作为首列进行查询匹配 。之后再使用row函数来自动更新查找值 , 从而得到一个关键字下的多个结果 。