我們都很清楚如何使用VLookup進行單一的匹配查詢,那麼有沒有辦法實現多重匹配查詢呢?比如下圖所示:我們需要在F2:F4的區域分別返回查詢A2:B6區域,對應產品A在B列的第1,2,3個值。
那麼怎麼實現呢?我們接下來進行一步步分解!
構建INDEX, AGGREGATE的組合函數!
最外層Index函數:Index函數可以返回一個序列中指定位置的數據。如圖,我們可以返回在B2到B6區域,排序第1的數。
第2個A出現在第4行,因此我們繼續輸入函數,將之前的1換成4,就能返回222了!
那麼接下來的問題就轉化為:如何智能的返回A在序列A2:A6的序號1,4,5呢? 接下來就要隆重推出Aggregate函數了!它於數組和ROW函數結合就能實現這個目的!
這個看似複雜的函數,我們怎麼理解它呢?
它的第一個參數function_num, 通過輸入不同的值,我們可以選取不同的計算邏輯,這裡因為我們要按順序從小到大選擇匹配的值,因此我們選擇SMALL函數,它的參數為15。
第二個參數option,可以讓我們選擇是否忽略隱藏和錯誤值,此時我們選擇3輸入。
第三個參數需要錄入分析的數組,我們在這裡使用了一個很複雜的數組公式,其中($A$2:$A$6=$E$2)會產生一個(TRUE,FALSE,FALSE,TRUE,TRUE)的數組,將其除以自身,會轉換成一個 (1, #div0, #div0, 1, 1)的新數組,讓我們再看(ROW($A$2:$A$6)-ROW($A$1)這個數組,它會形成一個從1開始的順序序列 (1,2,3,4,5),與之前的數組相乘,便得到了下圖H2:H6的數組序列了!
將這個順序序列嵌入到之前的Aggregate函數中作為第3個參數,這三個參數就很好的確定了需要分析的集合為(1, #div0, #div0, 4, 5),從當中從小到大選擇排位第k的數據,且忽略隱藏和錯誤!那麼這裡的k就是我們的第4個參數!
這裡的k我們使用ROWS($A$2:A2)函數,rows函數能返回所選區域的行數,通過鎖定$A$2, 我們通過下拉可以增加區域的行數,進而讓k能夠從1變成2,變成3。。。。。。
整合以後就是完整版的Aggregate函數了!
最後我們把aggregate函數替換之前Index函數的第二個參數,就形成了最終公式!怎麼樣,快來練習吧!
更進一步
在Excel2016裡,還有一種更為簡單的辦法,讓我們在下期節目介紹!
閱讀更多 FunInCode 的文章