EXCEL一对多数据查询,除了万金油公式,还有更多的经典组合套路,个个都能封神!

欢迎转发和点一下“在看”，文末留言互动!

置顶公众号或设为星标及时接收更新不迷路

小伙伴们好，今天和大家来聊一聊一对多查询这件事儿。顾名思义，一对多就是在给定一个已知条件的情况下，返回多个满足该条件的数据。

这种情况很多见，比如，要提取某个班级/小组/科室的人员名单，再比如，提取所有男性成员等等。

在以前，处理一对多问题的常用办法是万金油公式。这个公式应用场合非常广泛，大家也都很熟悉了。在今天的帖子中，除了万金油，还会向大家介绍几种平时我们不熟悉，或者不太常用的公式来解决一对多的问题。

原题目是这样的：

按照给定的部分，在部分下方提取部门内的人员。

办法有很多，你能想到哪一种？

一对多查询最常用的公式就是万金油公式。

万金油公式。这个我们曾经多次介绍过，今天这里就不再赘述了。

=IFERROR(INDEX($B$2:$B$8,SMALL(IF(A$2:A$8=E$2,ROW(B$2:B$8)),ROW(A1))-1),"")

在单元格E5中输入如下列公式，三键确认后向下拖曳即可。

=IFERROR(VLOOKUP(E$2&ROW(A1),IF({1,0},A$2:A$8&COUNTIF(INDIRECT("a2:a"&ROW($2:$8)),E$2),B$2:B$8),2,0),"")

这条公式的核心思路就是给部门后面加编号。

INDIRECT("a2:a"&ROW($2:$8))

利用INDIRECT函数返回7个单元格区域：A2:A2、A2:A3，...、A2:A8

COUNTIF(INDIRECT("a2:a"&ROW($2:$8)),E$2)

利用COUNTIF函数来统计E2单元格在这些区域中的数量。公式返回的结果是{0;0;1;2;2;3;4}。

A$2:A$8&COUNTIF(INDIRECT("a2:a"&ROW($2:$8)),E$2)

给部门后面添加编号。它的结果是{"计划部0";"计划部0";"人事部1";"人事部2";"生产部2";"人事部3";"人事部4"}

IF({1,0},A$2:A$8&COUNTIF(INDIRECT("a2:a"&ROW($2:$8)),E$2),B$2:B$8)

利用IF函数组成一个新的内存数组。在这个内存数组中有7行2列数据。第一列数据是上面那一步的结果；第二列数据是单元格区域B2:B8

它生成的结果是{"计划部0","唐太宗";"计划部0","宋太祖";"人事部1","汉武帝";"人事部2","清高宗";"生产部2","唐玄宗";"人事部3","汉高祖";"人事部4","明成祖"}

IFERROR(VLOOKUP(E$2&ROW(A1),IF({1,0},A$2:A$8&COUNTIF(INDIRECT("a2:a"&ROW($2:$8)),E$2),B$2:B$8),2,0),"")

接下来就简单了。VLOOKUP函数抓取数据，IFERROR函数屏蔽错误值。

在单元格E5中输入下列公式，并向下拖曳即可。

=VLOOKUP($E$2,OFFSET(A$1:B$1,MATCH(E4,B:B,),,100,2),2,0)

这条公式的核心思路是不断提供新的查找区域，以便返回查找值。

MATCH(E4,B:B,)

利用MATCH函数确定单元格E4在B列中的位置。注意，这里没有使用绝对引用，因此随着公式向下拖曳，E4是会动态变化的。

OFFSET(A$1:B$1,MATCH(E4,B:B,),,100,2)

利用OFFSET函数偏移、生成新的单元格区域。

在当前单元格E5时，MATCH(E4,B:B,)返回的结果是1，因此OFFSET函数返回的数据区域是A2:B102，所以VLOOKUP函数找到“汉武帝”。

当公式拖曳到E6时，MATCH(E5,B:B,)返回4，因此OFFSET函数返回的数据区域是A5:B105，所以VLOOKUP函数找到“清高宗”。

后面的类似

VLOOKUP($E$2,OFFSET(A$1:B$1,MATCH(E4,B:B,),,100,2),2,0)

接下来利用VLOOKUP函数来抓取数据就可以了。

在单元格E5中输入下列公式，并向下拖曳即可。

=VLOOKUP(E$2,INDEX(A:A,MATCH(E4,B:B,)+1):B9,2,)

这条公式由果果大佬提供。它和上面的那一条逻辑思路是相同的。都是提供新的查找区域。不同在于，这一条使用了INDEX函数。

在单元格E5中输入下列公式，并向下拖曳即可。

=VLOOKUP("*?",REPT(B$2:B$8,(A$2:A$8=E$2)-COUNTIF(E$4:E4,$B$2:$B$8)),1,)

这条公式由海鲜大佬提供。这条公式里有两点非常棒：

A$2:A$8=E$2

所有等于条件的数据。这个返回的结果是{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}。

COUNTIF(E$4:E4,$B$2:$B$8)

利用COUNTIF函数来统计单元格区域B2:B8中的数据在动态区域E4:E4中的数量。这里COUNTIF函数返回的结果如是{0;0;0;0;0;0;0}。

随着公式向下拖曳，动态区域E4:E4变成E4:E5，COUNTIF函数的结果也变成{0;0;1;0;0;0;0}。这表明第三个数据“汉武帝”已经被提取到了。

(A$2:A$8=E$2)-COUNTIF(E$4:E4,$B$2:$B$8)

这段公式的含义是，从满足条件的所有数据中，剔除已经提取到的数据。

当前单元格是E5时，A$2:A$8=E$2的结果是{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}，COUNTIF(E$4:E4,$B$2:$B$8)的结果是{0;0;0;0;0;0;0}，因此两两相减的结果是{0;0;1;1;0;1;1}。结果中“1”对应的就是人事部的成员。所以REPT函数返回{"";"";"汉武帝";"清高宗";"";"汉高祖";"明成祖"}。后面的VLOOKUP函数可以抓取到“汉武帝”。

当前单元格是E6时，A$2:A$8=E$2的结果还是{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}，COUNTIF(E$4:E4,$B$2:$B$8)的结果却变成了{0;0;1;0;0;0;0}，因此两两相减的结果就是{0;0;0;1;0;1;1}。你看这时第三位上的数字已经变为“0”了。所以REPT函数返回{"";"";"";"清高宗";"";"汉高祖";"明成祖"}。你看，这时“汉武帝”已经被清除了，因为在上一个单元格中“汉武帝”已经被提取到了。

后面的过程是一样的。

VLOOKUP("*?",REPT(B$2:B$8,(A$2:A$8=E$2)-COUNTIF(E$4:E4,$B$2:$B$8)),1,)

接下来就可以使用VLOOKUP函数来抓取数据了。这里VLOOKUP函数的第一参数是"*?"，这样写是有好处的。

“*”可以代表多个字符；“?”只能代表一个字符。它们组合在一起可以查找任意长度的字符串。当然，这类也可以写成“???”，不过这就失去了一定的灵活性。

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

我就知道你“在看”