「萬金油四人組Index Small If Row」， 會這個組合的Excel水平都不低!

聲明：以下內容屬於原創內容，如有轉載，請註明作者與出處。

相信大多數的excel水平還不錯的小夥伴會都聽到這個函數一組合，業內人普遍認為這個組合是萬金油，在處理大多數的問題的時候都會遇到這個函數組合。但是對於這個函數組合併不是所有的人都明白其中的意義，如果你懂這個函數組合，那麼說明你的函數水平已經超乎了平常人的水平。下面就兩個例子給大家說下關於這個函數組合的意義。

【例題】如下圖所示，將以下人員按部門進行歸類。分部以橫向與縱向兩個方向進行歸類。要求：使用函數公式進行歸類。

【解題方法與思路解析】

1、橫向

在F2單元格中輸入以下公式：

=IFERROR(INDEX($A$2:$A$14,SMALL(IF(F$1=$B$2:$B$14,ROW($B$2:$B$14),4^8),ROW(A1))-1),"")

按組合鍵確定，並填充。

2、橫向

在G8單元格中輸入以下公式：

=IFERROR(INDEX($A$2:$A$14,SMALL(IF($F8=$B$2:$B$14,ROW($B$2:$B$14),4^8),COLUMN(A1))-1),"")

按組合鍵確定，並填充。

以上兩個公式的用法都是一樣的，針對的是同一類的題目。該題目實際上就是一個一對多的查找匹配的問題，並且是反向的進行。如果正向也是同樣的方法。下面對橫向的公式進行解析：

(1)ROW($B$2:$B$14)，這裡的意思是一個數組，返回的值為：{2;3;4;5;6;7;8;9;10;11;12;13;14}，是一個1列13行的一個數組，此處也可以寫成row($2:$14),是同一個道理；row(A1)返回的是A1單元格行號，隨著公式的下拉分別返回對應的行的行號；

（2）IF($F8=$B$2:$B$14,ROW($B$2:$B$14),4^8)，這一部分是使用if函數進行條件判斷。$F8=$B$2:$B$14 返回值為：

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}，如果F8單元格的值等於區域$B$2:$B$14中的某一個單元格，則返回的為TRUE,否則為FALSE。大家翥知道，邏輯值是可以參與運算的，即TRUE=1,FALSE=0; 4^8是一個很大的數，即65533，也就是說已前版本的軟體的行數的最大值。這裡大家也可以使用比這個數更大的值，或者大於當前的非空區域的行數即可。

（3）SMALL(IF(F$1=$B$2:$B$14,ROW($B$2:$B$14),4^8),ROW(A1))-1這一步是使用samll取出IF($F8=$B$2:$B$14,ROW($B$2:$B$14),4^8)此運算結果的一組最小值，隨著row(A1)的下拉的時候的不斷地增大，最上值的取值分別從1，2，……，一直至F$1=$B$2:$B$14這個條件返回值為TRUE的最後一個最小值。即F8單元格所對應的區域的最後一個符合條件的值的下一個行號。而減去1的意思就是讓符合的值回到原位，或者SMALL(IF(F$1=$B$2:$B$14,ROW($B$2:$B$14),4^8),ROW(A1))-1這一步可以寫成：SMALL(IF(F$1=$B$1:$B$14,ROW($B$1:$B$14),4^8),ROW(A1))，這樣的話就不會產生錯位。

（4）利用index函數返回已經查找出來的行號在區域$A$2:$A$1中對應的值，如果以上產生錯誤值，那麼使用iferror容錯函數，將其錯誤值屏幕為空白。最後完成了查找。

另外，以上也可以使用vlookup進行查找，這裡再不做過多的描述，如果有意願者可以參考一下vlookup的一對多查找。