多对多查找能用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，如此条件区域可以随着公式填充而自动扩展。

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

不会！因为每个“1、2、3”等编号首次出现时都位于符合条件的数据行上，正好对应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：

相关推荐：

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

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

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

自定义格式从此不求人

版权申明：

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