Excel145 | INDEX SMALL，一對多查找的又一犀利組合

韓老師曾經寫過一篇：Excel | VLOOKUP一對多查找：不連續相同內容對應的多個數據一次提取，今天有朋友說：這個公式寫起來好麻煩啊！

我們的「office天天學」群里有位高手朋友說他更喜歡INDEX SMALL,那韓老師今天就把INDEX SMALL這一神組合完成一對多查找的方法給大家講一講。

結果是這樣的：

公式實現

在F2單元格輸入公式：

=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""，以<Ctrl Shift Enter>三鍵組合結束。

公式分步解析

我們以查找「張二」的消費記錄為例來分析：

第一步：

IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B))

用IF函數，建立一新的數組，這一新的數組建立的規則是：

如果A$2:A$13區域中的單元格內容等於E2單元格內容，則返回該單元格所在的行，否則返回整個工作表的行數。

所以：此部分返回的數組是：

{1048576;3;1048576;1048576;1048576;7;1048576;1048576;10;1048576;1048576;13}

可以看到：凡是A列單元格內容等於張二的，返回的都是對應的行數，不等於張二的，返回的都是工作表的行數1048576。

第二步：

SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)

在第一步形成的數組中，查找第第一小的數值。

用ROW(A1)做SMALL函數的第二個參數，即第幾小。

ROW(A1)是一個動態的數值，公式往下填充一行，行數加1，即當公式在F2單元格時，是ROW(A1)，當公式填充到F3單元格是，是ROW(A2)，當到F4單元格時，是ROW(A3)……

這樣，就在第一步的數組中找到了第1、2、3、4小的值，即3、7、10、13。

第三步：

INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))

當公式在F2單元格時，返回B列第3行的值，即張二的第一次消費記錄7478。因為公式中IF部分是數組計算，所以公式以<Ctrl Shift Enter>三鍵組合結束。

公式向下填充，得到B列第7、10、13行的值。

第四步：

INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""

在最後加上&""，這一步是容錯處理。用空單元格與空文本合併返回空文本的特性，將超出結果數量的部分不顯示出來。