EXCEL多結果查詢必學的函數(萬金油)

EXCEL中查詢函數想必第一個想到的就是VLOOKUP，再來就是INDEX與MATCH兩兄弟，但是這些函數都只能回傳一筆資料，如果一個條件有很多結果需要回傳呢?又沒有365的FILTER，這時就要使用大名鼎鼎的萬金油函數。

什麼式萬金油函數呢?主要是這個公式由IF、SMALL、INDEX、ROW所組合，這個組合太萬能所以依此命名?(我也黑人問號🤔)

這集就帶大家來了解這個公式的邏輯與如何使用吧，對於初學者來說，這個公式會比較吃力，有點耐心把他看完吧!

第一步:
F3=$D$3:$D$16="達標"
要判斷的範圍等於要判斷的關鍵字，如果符合的話就會回傳TRUE。

第二步:
F2=IF($D$3:$D$16="達標",ROW($D$3:$D$16))
利用IF與ROW將所有TRUE的位置回傳相對應的列號

ROW函數說明:回傳當前或參照列號
使用說明=ROW(參照)

IF函數說明:判斷符合與不符合回傳內容
使用說明=if(判斷式,符合,不符合)

第三步:
F3=SMALL(IF($D$3:$D$16="達標",ROW($D$3:$D$16)),ROW(A1))
利用SMALL配合ROW把符合的列號，由小到大列出來

SMALL函數說明:找出第幾小的數值
使用說明=SMALL(範圍,範圍內第幾小)

第四步:
INDEX(B:B,SMALL(IF($D$3:$D$16="達標",ROW($D$3:$D$16)),ROW(A1)))
把所有符合條件的列號使用INDEX抓出相對應的人員姓名

INDEX函數說明:回傳範圍指定位置內容
使用說明=INDEX(範圍,列位置,欄位置)

第五步:
F3=IFERROR(INDEX(B:B,SMALL(IF($D$3:$D$16="達標",ROW($D$3:$D$16)),ROW(A1))),"")
資料沒有這麼多，所以多出來的儲存格會出現#NUM，這時候可以用IFERROR將這些錯誤值顯示為空白

IFERROR函數說明:將錯誤取代成指定內容
使用說明=IFERROR(內容,錯誤時顯示)

最後要特別注意，此公式是陣列函數，如果版本沒有動態陣列，再輸入此公式時需要按CTRL+SHIFT+ENTER取代ENTER輸入才可以正常使用哦!

如果看圖面與文字說明還是有點不能理解，那麼下載檔案，點開下方影片一步一步跟著做吧!

檔案下載

覺得分享有幫助到你在工作上提升效率
可以追蹤我的方格子或請我喝杯咖啡
也可以訂閱EXCEL設計新思維
可以看到更多增加效率的好方法唷~

職場上有相關問題，加入我的line社群討論唷~

追蹤我的IG

追蹤我的粉絲團

追蹤我的Dcard