不用輔助列一對多查詢 _不用

舉個例子，左邊是公司各部門的員工名單數據，現在我們需要根據部門，來查找匹配出該部門下的所有員工姓名，如下所示：

文章插圖
方法一：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))

文章插圖
關于這個技巧，你學會了么？動手試試吧！