二维码 购物车
部落窝在线教育欢迎您!

COUNTIFS+VLOOKUP多对多查找更简单

 

作者:ITFANS来源:部落窝教育发布时间:2023-11-21 21:03:09点击:375

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。
编按:

多对多查找能用VLOOKUP吗?可以!只是需要COUNTIFS的帮助,把多对多查找变成一对一查找。

 

VLOOKUP作为查找界已过气的杠把子,我们仍然常使用它查找数据。但是VLOOKUP存在两点不足:

①VLOOKUP从上到下查询,且只返回第一个匹配结果——不能返回多个匹配结果。

②VLOOKUP只支持单条件查找,如果是多条件必须要链接成单条件并改造查找区域,公式书写繁琐。

因此,VLOOKUP无法直接进行多对多查找。

但我们可以创作条件,将多对多查找变成一对一查找,就可以用VLOOKUP了。

 

比如下面表格中,需要根据分部、组别、部门三个条件,将符合条件的员工查找出来。

 

 

我们可增加辅助列,用COUNTIFS函数为符合条件的每行数据添加上不同的编号,再通过编号查找员工,如此就将多对多查找变成了一对一查找。

 

Step 01 添加编号

在“部门”前添加辅助列。在A2单元格输入公式=COUNTIFS($B$2:B2,$I$2,$C$2:C2,$G$2,$D$2:D2,$H$2),然后下拉填充。

 

 

公式中的条件区域都是锁定起点单元格,不锁定终点单元格,如$B$2:B2,如此条件区域可以随着公式填充而自动扩展。

 

现在每行数据前都有编号。有伙伴会问“那些不符合条件的也编号了,不会影响吗?”

不会!因为每个“123”等编号首次出现时都位于符合条件的数据行上,正好对应VLOOKUP的第一个不足“从上到下查询,且只返回第一个匹配结果”。

 

 

Step 02 完成查找

定位到J2输入公式=IFERROR(VLOOKUP(ROW(A1),A:E,5,0),"")并下拉即可。

 

 

看来有了COUNTIFS的加持,VLOOKUP做多对多查找也很简单啊!

不只如此,在COUNTIFS的加持下,VLOOKUP甚至能完成如">5""<>A2"等比较判断作为条件的查找。

 

比如在下表中,需要将组别里包含“成本”,且入职日期在2022-11-21之前的员工找出来。

 

 

定位到A2输入公式=COUNTIFS($D$2:D2,"成本*",$F$2:F2,"<2022-11-21")并向下填充。

然后在J2输入公式=IFERROR(VLOOKUP(ROW(A1),A:E,5,0),"")下拉即可。

 

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

跨多表的多对多查找有了它也不难

妙用FILTER函数实现任意关键词查询数据

含万金油公式在内的经典查找公式合集

自定义格式从此不求人

版权申明:

本文作者ITFANS;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。