一對多查詢?少不了這兩刷子

我們對數據進行查詢時，經常會使用VLOOKUP函數。

但有時，我們會碰到這樣的問題，提取符合條件的結果是多個，而不是一個，這時候VLOOKUP就犯難了。

舉個例子，如下圖，左側A1:C10是一份學員名單表，現在需要根據F1單元格的「EH圖班」這個指定的條件，在F2:F10單元格區域中，提取該班級全部學員名單。

今天說一個函數查詢方面的萬金油套路：Index+Small。

F2單元格輸入以下數組公式，按住Ctrl+Shift鍵不放，再按回車鍵，然後向下填充：

=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1))),"")

這個公式看起來可就比上面那個VLOOKUP的解法苗條養眼多了，坦白的說，很搭俺星光十年後的匪號——小清新。

1公式講解

IF(A$1:A$10=F$1,ROW($1:$10),4^8)

這部分，先判斷A1:A10的值是否等於F1，如果相等，則返回A列班級相對應的行號，否則返回4^8，也就是65536，一般情況下，工作表到這個位置就沒有數據了。

結果得到一個內存數組：

{65536;2;3;65536;65536;65536;65536;8;65536;10}

SMALL函數對IF函數的結果進行取數，隨著公式的向下填充，依次提取第1、2、3……n個最小值，由此依次得到符合班級條件的行號。

隨後使用INDEX函數，以SMALL函數返回的行號作為索引值，在B列中提取出對應的姓名結果。

當SMALL函數所得到的結果為65536時，意味著符合條件的行號已經被取之殆盡了，此時INDEX函數也隨之返回B65536單元格的引用，結果是一個無意義的0，為了避免這個問題，可以在公式後面加上一個小尾巴 &""

利用&」」的方法，很巧妙的規避了無意義0值的出現，只是當查找結果為數值或日期時，這個方法會把數值轉變為文本值，並不利於數據的準確呈現以及再次統計分析。

最後留下一道練手題，如下圖，根據A1:C10區域的數據，將E列相關班級的姓名，填充到F2:I5區域。

今天就這樣吧，我是人見人愛的星光，小夥伴們下次再見嘍。。。。。

圖文製作：看見星光

2016，你最牛！

《HR玩轉Excel》免費公開課，12日、13日晚8點準時開始，點擊【閱讀原文】現在報名吧！