Excel一對多查找經典公式解讀

06-01

在工作中，有時候我們需要進行一對多查找。Vlookup、lookup默認是一對一查找，無法進行一對多查找。今天我們就一起來學習Excel一對多查找經典公式：index small if套路公式。

公式：=INDEX(B:B,SMALL(IF($A$2:$A$7=$D$2,ROW($2:$7),65536),ROW(A1)))&""

公式解讀：

很多新人一看到這麼長的公式就暈了，其實我們可以用快捷鍵F9進行公式部分運算，就能明白其中的道理了。

Small（IF($A$2:$A$7=$D$2,ROW($2:$7)，65536)，row（a1））

65536是excel中極大的一個數，if函數是判斷A2：A7中如果等於D2，那麼就返回其行號，row函數是返回行號的函數。

Row（a1）等於1，當公式往下拖動後，會變成row（a2）、row（a3）…,產生一個自然數的序列。

Small函數語法如下：SMALL(array,k)

k 為返回的數據在數組或數據區域里的位置(從小到大)。

array 查找的數據區域。

整個公式的意思是返回滿足條件的第一小值，第二小值…。

當我們得到其行號之後，再結合index函數來查找需要的值，如此就是一對多查找。

最後我們進行容錯處理，如果出現錯誤，用空白單元格來表示。也就是在公式後面加上&""

因此整個公式為：=INDEX(B:B,SMALL(IF($A$2:$A$7=$D$2,ROW($2:$7),65536),ROW(A1)))&""

GIF操作效果如下：

Index small if公式為萬金油公式，在excel中運用十分廣泛。今天只是小試牛刀，請大家多思考多練習。如果暫時不懂，請記住該公式，套用即可。