匹配出“一對多”的資料對應值，橫向一一排開

例如原始資料左側的兩列，每個歌手分別對應著多首歌曲，只用最基礎的VLOOKUP只能匹配第一個，現在需要以歌手為匹配物件，一次性匹配每個歌手所對應的每首歌曲。

思路為：一個歌手分別對應了多首歌曲，意味著“這個歌手在該列出現了多少次就等於他有多少首歌，也代表需要匹配的次數”，所以我們先統計出每個歌手的名字分別出現了多少次並記錄下來。再使用VLOOKUP多條件查詢，利用“歌手”列和“重複次數”列，兩個條件去查詢對應的歌曲名稱。

第1步：

輸入=COUNTIF(A$2:A2,A2)，並向下自動填充，輸入右引用符號“$”符號的目的是，讓下拉自動填充的時候，統計重複次數的範圍，自動選擇第一行到當前行，這樣統計出來的重複次數就是“當前該歌手名字的重複次數”，也就是當前該歌手的名字出現了第幾次了。

（王菲-傳奇的對應行，王菲出現了第4次）

第2步：

在如圖的I2單元格輸入=IFERROR(VLOOKUP($H2&COLUMN(A$1),IF({1,0},$A$2:$A$20&$C$2:$C$20,$B$2:$B$20),2,0),"")

按Ctrl+Shift+Enter鍵轉換為陣列公式計算結果，並向右向下填充，即可完成。

原理如下：

公式的主要部分為=VLOOKUP($H2&COLUMN(A$1),IF({1,0},$A$2:$A$20&$C$2:$C$20,$B$2:$B$20),2,0)

VLOOKUP的查詢物件是兩個條件：第一個條件是歌手名稱，即是單元格H2，按下兩次F4向右不改變引用關係；第二個條件是重複次數，公式為COULUMN(A$1)——統計單元格對應的列數，這個是什麼意思呢：周杰倫重複的第幾次就代表了第幾首歌，也就代表著“向右拉到第幾列”，所以索性使用COULUMN(A$1)，按一下F4向右改變引用關係，取A1單元格的列數為1，向右拉就代表了“周杰倫重複第二次”、“周杰倫重複第三次”的查詢條件。

看，查詢物件其實是“周杰倫1”也就意思周杰倫的第一首歌。

好了，VLOOKUP的第二組成部分是查詢範圍，這裡的公式是IF({1,0},$A$2:$A$20&$C$2:$C$20,$B$2:$B$20)，這下看著複雜了，其實這是EXCEL裡經典的IF{1,0}陣列公式，意思是，把A列歌手和C列重複次數列連線起來，當成一列，與歌曲名稱B列是對應關係列。

這裡有必要插入一下IF{1,0}的意思

前面的{1,0}是IF公式利用水平陣列分別做邏輯判斷，1代表TURE，0代表FALSE，具幾個例子：

=IF(1,哈哈,呵呵)，返回為“哈哈”

=IF(0,哈哈,呵呵)，返回為“呵呵”

=IF({1,0},哈哈,呵呵)，返回為水平陣列“{哈哈，呵呵}”

就是先判斷數組裡的第一個數字1結果為哈哈，第二個數字0結果為呵呵，輸出的結果仍為陣列{哈哈，呵呵}。

=IF({1,0},A2:A20,B2:B20)，結果就是返回A列和B列兩列陣列，具體值為

{A2,B2;

A3,B3;

A4,B4;

A5,B5;

……}

那麼這裡

IF({1,0},$A$2:$A$20&$C$2:$C$20,$B$2:$B$20)，結果還是返回兩列陣列：

{A2&C2,B2;

A3&C3,B3;

A4&C4,B4;

A5&C5,B5;

……}

那對應查詢物件A2&C2是不是就像常規的VLOOKUP一樣了？只不過把兩列資料當成一列了。

最後一個IFERROR只是美化公式，當單元格向右拉拉到錯了就顯示為空。

=IFERROR(VLOOKUP($H2&COLUMN(A$1),IF({1,0},$A$2:$A$20&$C$2:$C$20,$B$2:$B$20),2,0),"")

記得按下Ctrl+Shift+Enter鍵。

嗯，結束了。

建議用exact函式驗證一下，你的B2和D2到底是不是一樣。我覺得vlookup公式的冒號大部分人是手工選擇範圍的吧（不清楚到底是不是），所以不會這裡出問題。而至於逗號的各種輸入法，一般來說中英文輸入逗號不會影響公式。所以我懷疑是資料本身有問題。如果Exact出現false.可以用CLEAN函式清洗一下試試。