Excel中一對多查找,絕對不能錯過的高級技能!

  在Excel中進行一對多查找,一直是很多人心中的痛。

所以今天罌粟姐姐給大家分享兩組函數組合,幫助大家熟練掌握一對多查找這一高級職場技能。

案例:

現有學生成績單一份。

Excel中一對多查找,絕對不能錯過的高級技能!

圖:原始數據

現需要在I4-N9區域根據I2生源地查找所有該生源地的記錄。

Excel中一對多查找,絕對不能錯過的高級技能!

圖:預計做到效果

方法1:

VLOOKUP COUNTIF

操作步驟:構造輔助列A列,在A2單元格輸入公式=COUNTIF(C$2:C2,C2)&C2,用COUNTIF函數將生源地出現的次數和生源地聯繫起來,形成序號 生源地的形式。

Excel中一對多查找,絕對不能錯過的高級技能!

圖:構造輔助列

在I5單元格輸入公式

=IFERROR(VLOOKUP(ROW(A1)&$I$2,$A:$G,COLUMN(B1),0),”)

橫向、縱向進行單元格填充,即完成一對多查找。

Excel中一對多查找,絕對不能錯過的高級技能!

圖:完成一對多查找

方法2:

INDEX SMALL

這種方法在上周一教程中提到過,再次單獨拿出來給大家分享,可以與方法1進行對比,加深記憶。

計算方式:在D5單元格輸入公式=INDEX($B:$B,SMALL(IF($A$2:$A$10=$D$2,

ROW($A$2:$A$10),4^8),COLUMN(A1)))&”,按Ctrl Sheet Enter三鍵結束運算,向右拖動公式即可完成同一生源地學生名單的查找。

其中,利用SMALL函數來定位所有D2在第一列的位置,COLUMN(A1)用來顯示第幾個D2,這樣在拖動D5單元格填充柄往右填充公式時,在D5時為COLUMN(A1)即1,第一個D2;E2時為COLUMN(B1)即2,第二個D2,以此類推。在這個公式末尾,添加&”,是為了做到公式在向右拖動的過程中如果沒有匹配值就用空格代替。

Excel中一對多查找,絕對不能錯過的高級技能!

圖:INDEX與SMALL函數嵌套做到一對多查找