一對多查找，用 Vlookup 函數太Out了！

05-30

我們以前介紹過用Vlookup函數完成一對多查找：

【例】如下圖所示，要求在F列查找「張明城」的個人消費記錄

數組公式：

{=VLOOKUP(F$1&ROW(A1),IF({1,0},$B$2:$B$10&COUNTIF(INDIRECT("b2:b"&ROW($2:$10)),F$1),$C$2:$C$10),2,)}

估計只有十分之一的同學能看懂上面的公式原理，真的需要這麼複雜嗎？NO! 其實我們可以不用Vlookup函數的：

{=INDEX(C:C,SMALL(IF(B$2:B$10=F$1,ROW($2:$10)),ROW(A1)))}

公式解析：

IF(B$2:B$10=F$1,ROW($2:$10))：如果B列的姓名和F1的姓名相同，就返回它的行號。不相同的返回FALSE
Row(a1)：是返回A1的行號1，如果向下複製會變為 Row(a2)，返回2，其實用它的目的是當公式向下複製時可以生成序號：1，2，3...然後取符合條件的第1個行號，第2個行號...
SMALL(): 從符合條件的行號中從小到大，逐個提取符合條件的行
INDEX() :根據取得的行號從C列提取值
{ }：數組公式（含有逐一運算的公式）需要按ctrl shift enter 輸入大括弧（一定要是自動生成的，不能手輸入大括弧）。

這麼難，學這個公式有什麼用？當然有用！

很多同學問，輸入總表怎麼能自動生成分表，而且修改總表分表也可以自動修改？用index match組合公式根據條件提取數據生成分表。

=IFERROR(INDEX(消費明細!A:A,SMALL(IF(消費明細!$B$2:$B$10=$B$1,ROW($A$2:$C$10)),ROW(消費明細!A1))),"")

註：這裡用IFERROR函數屏蔽公式錯誤值

蘭色說：如果工作中經常複雜的求和、核對、查找難題，建議學習一下Excel函數嵌套和數組運算。這些公式看著複雜無比，其實懂得函數的運算套路，一點都不難的。至少比上大學時學的微積分，不知道要簡單多少倍。