我們以前介紹過用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,)}
估計只有十分之一的同學能看懂上面的公式原理,
{=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函數遮罩公式錯誤值