每日Excel分享（函數）| 拆解一對多查詢公式，讓村長帶你領略數組公式的魅力

感

謝

關

注

前言

工作中我們一般都用VLOOKUP函數進行查找，但是當查找的結果同時有多個時，VLOOKUP只能返回第一個出現的結果，如果要返回所有結果就需要用比較複雜的數組公式。

說到數組公式，沒接觸過的人可能會覺得很難理解，很複雜，但是當我們真正了解數組公式的結構及運算原理時就會發現，數組公式其實很簡單。不信你接著往下看，看村長給你拆解一對多查詢公式，帶你領略數組公式的魅力！

1、判斷是否符合條件

我的Excel

C2單元格輸入=IF(A2=$D$2,ROW(A2),9^9)，公式下拉，公式的意思就是判斷A列的姓名是否與D2單元格（條件）相等，如果相等則返回當前行的行號，否則返回9^9。然後我們看公式的結果我們會發現符合條件的結果都是當前行的行號，不符合條件的結果都是3.87E 08（這是科學計數法，一個極大的值），接下來我們將公式變換一下

選中C2:C21區域，公式改為=IF($A$2:$A$21=$D$2,ROW($2:$21),9^9)，按CTRL SHIFT 回車鍵三鍵結束，公式的意思是分別將$A$2:$A$21的每一個單元格與D2對比，相等的時候分別對應返回2-21，否則返回9^9。

這是數組公式的精髓所在，IF的第1參數進行了多次對比運算，最終IF會得到多個結果存放在一個集合里，這就是數組的多重運算。

2、將判斷的結果按從小到大排序

我的Excel

描述：D2單元格輸入=SMALL($C$2:$C$21,ROW(A1))，公式下拉，公式的意思就是用SMALL函數依次獲取第1、2、3....小的值，也就是將數據按從小到大重新排序。

3、用INDEX函數根據行號位置查找內容

我的Excel

描述：E2單元格輸入=INDEX(B:B,D2)，公式下拉，公式的意思就是根據D列獲取到的行號位置返回B列對應位置的內容

PS：3.87E 08行已經遠遠超出了工作簿允許的最大行數1048576行，所以屬於無效引用，當INDEX引用該行是返回「#REF!」錯誤值。

4、嵌套整合公式3-2-1

我的Excel

完整的公式嵌套，將前面1-2-3逆向替換即可完成嵌套，

1、用=SMALL($C$2:$C$21,ROW(A1))替換=INDEX(B:B,D2)裡面的D2；

2、用=IF($A$2:$A$21=$F$2,ROW($2:$21),9^9)替換=SMALL($C$2:$C$21,ROW(A1))裡面的$C$2:$C$21

詳細請看上圖

結束語

昨天有個關注了公眾號的朋友留言說想看下查找引用，多重計算，公式嵌套，所以今天我特地寫了這篇文章，公式嵌套與公式拆解屬於兩種不同的操作，如果我們真正想要學好公式，就必須要學會如何拆解公式，將嵌套的公式拆解成一個個零件，然後再組裝起來，重新完成嵌套。

每日一歌

張靚穎《我的夢》

推薦閱讀：