不用辅助列一对多查询_不用

举个例子，左边是公司各部门的员工名单数据，现在我们需要根据部门，来查找匹配出该部门下的所有员工姓名，如下所示：

文章插图
方法一：Vlookup+辅助列
首先我们来看下添加辅助列的情况，我们在A列前面插入一列，命名为辅助列，然后在辅助列输入的公式是：
=C2&COUNTIFS($C$2:C2,C2)

文章插图
counifs是计数公式，我们第一个C2进行固定引用，当我们将公式下拉填充的时候，起始数据区域还是C2 ，结束匹配是C6，也就是从C2到C6里面找，有多少个市场部的，这个时候已经是第3个市场部了，所以结果是市场3

文章插图
通过辅助列的构建，我们得到了唯一值
【不用辅助列一对多查询】所以我们需要查找各个部门的第1个员工的话，就把查找值改成F2&1，既市场1的员工
=VLOOKUP(F2&1,$A:$D,4,0)

文章插图
如果想查找第2个员工的话，就是市场2，第3个就是市场3对应的员工，为了让我们公式能够向右填充，所以我们使用Column(A1)公式来替代数字1，Column()公式是返回单元格在第几列,A1在第1列，所以结果是1 ，
然后E1单元格要按3下F4进行固定引用列的位置，IFERROR公式用来屏蔽错误值，所以最后我们使用的公式是：
=IFERROR(VLOOKUP($F2&COLUMN(A1),$A:$D,4,0),"")

文章插图
2、Vlookup公式不用辅助列
如果说我们想不用辅助列，要用VLOOUP实现一对多查询的话，直接使用公式：
=IFERROR(VLOOKUP($E2&COLUMN(A1),IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$E2),$C$1:$C$100),2,0),"")

文章插图

接下来是理解过程：
其实和插入辅助列一样，首先要构建一个虚拟数组，也就是说累计计数虚拟存在，但是不体现在单元格中，我们用虚拟数组构建的公式是：
$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$E2)，为了方便理解，我们把这个公式放在单元格，就得到了如下的一个虚拟数组

文章插图
以上只得到了查找列，我们还要把结果列放进行，所以我们用IF({1,0})来构建，公式为：
=IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$G2),$C$1:$C$100)

文章插图
最关键的就是解决了第二参数，虚拟数据源的构建，最后就是我们只需要用VLOOKUP，对查找值+COLUMN公式，就能得到我们想要的结果了
=IFERROR(VLOOKUP(查找值&COLUMN(),虚拟数组,2,0),"")
3、FILTER+TRANSPOSE
如果你的EXCEL版本够高，我们就可以用FILTER+TRANSPOSE公式，快速查找，我们输入的公式是：
=TRANSPOSE(FILTER(C:C,B:B=E2))