點擊上方

藍色

文字  關(guān)注我們吧！

送人玫瑰，手有余香，請將文章分享給更多朋友

動手操作是熟練掌握EXCEL的最快捷途徑！

上周剛和大家講了一對多查詢的的方法。鑒于一對多查詢在實際工作中經(jīng)常出現(xiàn)，今天就再次強調(diào)一下一對多查詢的函數(shù)組合拳！由于VLOOKUP函數(shù)那部分需要添加輔助列，相對比較簡單，不再詳細解釋了。

INDEX函數(shù)+SMALL函數(shù)+IF函數(shù)法

完整的公式應(yīng)該是“=IFERROR(INDEX($B$2:$B$17,SMALL(IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1,100),ROW(A1))),"")”，CTRL+SHIFT+ENTER回車。

思路：

• 利用IF(A$2:A$17=$J$2,ROW($A$2:$A$17)構(gòu)造出一個數(shù)組{2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;FALSE;FALSE;FALSE;FALSE},再減去1，就得到“天龍八部”這部書所在的行號

• IF公式中第三個參數(shù)“100”保證了當邏輯值是FALSE是，返回值是100，這個值足夠大，至少要大過所有數(shù)據(jù)的行數(shù)，以保證FALSE對應(yīng)的行不會被INDEX函數(shù)抓取到

• 其余的INDEX函數(shù)和IFERROR函數(shù)就相對簡單了，不再贅述

注意：在IF函數(shù)中第三個參數(shù)如果不書寫，也是可以達到目的的。IF函數(shù)部分可以直接寫成IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1)也是可以的。

OFFSET函數(shù)+SMALL函數(shù)+IF函數(shù)法

和上面的例子相同，完整的公式為“=IF(ROW()-1>COUNTIF($A$2:$A$17,$J$2),"",OFFSET($B$1,SMALL(IF(A$2:A$17=$J$2,ROW($A$2:$A$17)-1,100),ROW(A1)),0))”，CTRL+SHIFT+ENTER回車。

思路：

• IF部分的公式和上例是相同的，請參看上例

• OFFSET函數(shù)利用SMALL函數(shù)的返回值進行偏移

• 著重講一下ROW()-1>COUNTIF($A$2:$A$17,$J$2)這一部分。它是IF函數(shù)邏輯判斷的第一個參數(shù)，含義是：當當前的行號-1（因為是從第二行開始的）大于某本著作在數(shù)據(jù)區(qū)域中出現(xiàn)的總次數(shù)時，意味著所有符合要求的數(shù)據(jù)都已經(jīng)被抓取了，就返回空值
  

同樣地，這里的IF函數(shù)第三個參數(shù)也可以省略。

文章推薦理由：

一對多查詢時如此地重要，需要你牢記公式組合套路！

推薦閱讀：

一對多查詢？這里的方法是比較全的，值得你收藏！

漲知識了，篩選功能原來可以這樣用！

你知道每個月你家交多少電費嗎？

高級篩選？公式表示不服！

總結(jié)篇-IF函數(shù)實用終極帖