两位Excel函数大咖约个简餐，结果一不小心就解决了个历史难题，套路真深…

个人微信号 | （ID：ExcelLiRui520）

微信公众号 | Excel函数与公式（ID：ExcelLiRui）

微信服务号 | 跟李锐学Excel（ID：LiRuiExcel）

VLOOKUP和COUNTIF都是各自领域的大咖人物。

一提到数据查找，人们最常想到的就是VLOOKUP；

一提到数据统计，人们最常想到的就是COUNTIF；

所以，这两位的忙碌程度，可想而知......

难得今儿个有机会，两位函数大咖约了个简餐，聊聊当下职场里那些事儿，一不小心就说到一个困扰过80%以上白领的历史难题。

一对多查询问题：即数据查询结果有多个时，需要返回所有符合条件的数据。

简单模拟个场景如下图所示，要求根据查询类别，返回该类别下的所有商品。

虽说VLOOKUP就是专干查询这行的，但他只能返回第一个查询结果，要想把符合条件的结果全部返回，就无能为力了；

单用VLOOKUP的结果，如下图所示。

今儿个正好遇上COUNTIF，两人一联手，难题立马变成纸老虎，轻松破解！

聪明的你，知道他俩是怎么做的吗？

下面结合案例展开讲解，还会有扩展案例，所以正文会比较长，没时间一气看完的同学，可以分享到朋友圈给自己备份一份。

除了本文内容，还想全面、系统、快速提升Excel技能，少走弯路的同学，请搜索微信公众号“LiRuiExcel”点击底部菜单，或下方二维码进知识店铺。

更多不同内容、不同方向的Excel视频课程

获取

（长按识别二维码）

一、写好公式后的效果演示

写好公式以后，一对多查询妥妥实现。

在F2单元格选择查询类别后，所有该类别下的商品在G列全部列示出来了，动图演示如下所示。

即使数据源里面是乱序排列，也不妨碍结果显示，可见这个公式兼容性极强。

下面具体介绍公式思路及写法。

二、创建公式辅助列

当数据源现有条件不足以解决问题时，我们可以自己创造条件再写公式。

用这个思路，我们来思考一对多查询问题，发现关键点是需要把多次出现的同一个数据区分开来，这样才可能查询到多个对应的结果。

就拿商品类别中的蔬菜来说吧，我们要把第一次出现的标识为“蔬菜1”，第二次出现的标识为“蔬菜2”，依此类推，每个类别后面都带上第几次出现的序号。

这种效果，我们可以用COUNTIF函数实现，如下图A列所示。

=COUNTIF(C$2:C2,C2)

然后把商品类别和出现序号连接在一起，用如下公式。

=C2&A2

效果如下图B列所示。

这样我们就成功区分开了多次出现的商品类别，得到了商品类别&序号的联合列，即上图中B列。

到这一步问题就变得简单了，按照B列的联合条件查询，就可以把一个类别下的多个结果分别查询出来了。

三、函数组合公式写法

万事俱备只欠东风，联合查询条件区域已经搞定了，只差查询用的联合条件了。

要在G列列示所有结果，即：

在G2单元格放置第一个结果，即蔬菜1对应的结果；

在G3单元格放置第二个结果，即蔬菜2对应的结果；

依此类推。

公式如下：

=VLOOKUP(F$2&ROW(A1),$B$2:$D$16,3,0)

将公式向下填充，得到的计算结果如下图所示。

公式原理解析：

使用F$2&ROW(A1)作为VLOOKUP第一参数，随着公式向下填充，一次返回蔬菜1、蔬菜2、......蔬菜5，按此条件在联合区域里面查询即可。

即使数据源乱序，此公式也可以正确返回结果，如下图所示。

这种思路很重要，理解后可以结合各种场景扩展使用。

希望这篇文章能帮到你！

这么多内容担心记不全的话，可以分享到朋友圈给自己备份一份。

更多经典的实战技能，已整理成超清视频的系统课程，方便你系统提升。