怎么用VLOOKUP搞定一对多的匹配问题

作者：郅龙来源：部落窝教育发布时间：2021-06-29 10:29:41点击：16861

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：

VLOOKUP作为万能的查询函数，被广泛运用于一对一的数据查询中，可是一旦碰到一对多的查询问题，就显得有点捉襟见肘了。真的是这样吗？其实，VLOOKUP对一对多的问题也可以轻松解决！下面，一起来看看真正的高手怎么简单有效地用VLOOKUP制作一个一对多的动态查询表吧！

VLOOKUP函数大家应该都会用，这个函数平时可是帮我们大大提高了工作效率呢。通常用VLOOKUP都是一对一的匹配，但也有时候会遇到一对多的问题，很多人就不知道该怎么办了。

其实加一个辅助列的话，用VLOOKUP很容易就能搞定一对多的匹配问题，今天就这个问题介绍两个思路，希望能对大家有所帮助。

先来看看一对多匹配的效果图：

一、思路一

1.添加一个辅助列A列，并在A2中输入公式：=B2&COUNTIF($B$1:B2,B2)后下拉填充。

公式解析：

①在这个公式中，COUNTIF函数实现的是对某个区域中的某个值进行计数，所以COUNTIF($B$1:B2,B2)实现的是一个编号的效果。

②公式中的区域写法是$B$1:B2，当公式下拉的时候，区域的范围会随着增加。得到的结果就是辅助列中部门后面的那些数字，表示部门是第几次出现。

③用&符号把部门和第几次出现连接起来，作为VLOOKUP的查找范围的首列。

2.在单元格F2中建立下拉菜单，在单元格G2中输入公式：=IFERROR(VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(C1),0),"")，把公式往右填充至H列，再往下填充，就实现了一对多的匹配。

公式解析：

①在VLOOKUP($F$2&ROW(A1),$A:$D,COLUMN(C1),0)这部分，查找条件是$F$2&ROW(A1)。

②F2是要查找的部门，ROW(A1)是A1单元格的行号，也就是1，下拉时会变成2、3……，组合以后会形成和辅助列类似的结果，即“部门名称”+“行号序列”，此时就实现了将一对多匹配的问题变成一对一匹配的问题。

③COLUMN(C1)是为了方便公式可以右拉到H列，不用再为H列重新编写公式。

④IFERROR让公式不显示错误值。

方法1是一个比较常规的思路，接下来要说的方法2，就比较新颖了。

二、思路二

1.添加一个辅助列A列，并使用公式：=A1+(B2=$F$2)

这个辅助列的公式看上去非常简单，但很多小伙伴可能会比较蒙，这是什么意思呢？

其实在这个公式中，是利用了比较运算得到一个逻辑值，再利用逻辑值计算得到一组数字。

公式解析：

这一组数字和方法1中的数字含义有些类似，也是要查找的部门出现的次数，只不过是反推。即当F2中出现了数据，且通过逻辑判断在B列中找到时，再与当前单元格相加。比如，下图中，当F2为“销售部”时，B列有相同的单元格时，相对应的A列单元格内依次计数为1—4；当B列没有与之相同的单元格时，相对应的A列单元格内显示为0。