EXCEL函數愛好者最喜歡的INDEX SMALL IF組合

EXCEL公式愛好者必須要掌握的INDEX+SMALL+IF組合查詢公式

EXCEL中經常要用到一對多查詢，這裡可以用INDEX+SMALL+IF組合公式來實現一對多的查找。

如下圖所示，要求查詢每個部門所對應的人員。

在E2單元格中輸入數組公式：

=IFERROR(INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10=$D2,ROW($B$1:$B$10),9^9),COLUMN(A1)))&"","")，輸入完畢後按CTRL + SHIFT +ENTER結束輸入。

將公式向右、向下拖拉填充，即可完成一對多的查詢要求。

if返回一個一維數組，IF($B$1:$B$10=$D2,ROW($B$1:$B$10),9^9)，如果與D2單元格值相同則返回B列單元格的行號，如果為假，則返回9^9即387420489。所以if函數最終返回值要麼是指定單元格所對應的行號，要麼是9^9。

SMALL返回數組中第K個最小值，SMALL（array,k)，此處將if函數返回的數組作為SMALL的第一參數，返回數組中第COLUMN(A1)個最小值，E列為COLUMN(A1)=1，即第一個最小值，SMALL(IF($B$1:$B$10=$D2,ROW($B$1:$B$10),9^9),COLUMN(A1))，所以E2單元格中SMALL函數的返回值是IF函數里的行號"2"。

最後的INDEX函數返回行列交叉單元格的值，index(array,row_num,[column_num])，small函數作為index的第二個參數，與array即$A$1:$A9交叉處即為需要返回的值。以E2為例，INDEX函數返回第1列、第2行的值，即"林沖"。

關於IFERROR，是為了進行錯誤處理而加在最外層的，避免單元格中顯示錯誤值。

由於這個公式經常被用到，可以固化成以下格式：

= INDEX(區域，SMALL(IF(條件，行號數組，9^9)，ROW(A1)))

推薦閱讀：