一對多查找(2017版)
2017要和2016區別開來
問題:為什麼我用VLOOKUP函數不能一對多查找?
針對該群友的問題,用VLOOKUP函數解題,另外介紹其他兩種常用查找函數解題公式。
一、函數VLOOKUP
公式:G3=IFERROR(VLOOKUP($F$3&ROW(A1),IF({1,0},$B$2:$B$22&COUNTIF(INDIRECT("b2:b"&ROW($2:$22)),$F$3),C$2:C$22),2,),""),三鍵結束。
VLOOKUP(查找值,查找區域,返回值在查找區域的第幾列,查找方式)
第一參數查找值F3&ROW(A1)得到{"株洲花花1"},當下拉時變成F3&ROW(A2)得到{"株洲花花2"},也就是說要找第幾個株洲花花的值時後面就是幾;
IF({1,0},$B$2:$B$22&COUNTIF(INDIRECT("b2:b"&ROW($2:$22)),$F$3),C$2:C$22)部分是VLOOKUP第二參數查找區域,INDIRECT函數返迴文本字元串所指定的引用,INDIRECT("b2:b"&ROW($2:$22))部分中ROW(2:22)就是2至22,用b2:b連接就是返回b2:b2、b2:b3…b2:b22這樣一個引用,
再用函數COUNTIF統計這個引用中滿足條件的F3單元格內容"株洲花花"的個數,然後連接B2:B22,得到
查找區域至少要包含查找值所在的列也要包含返回值所在的列,而查找值所在的列就是函數COUNTIF部分,返回值所在的列是C列,
VLOOKUP第二參數不能由兩個區域單獨組成,所以用到IF{1,0}來構造一個數組;
返回值在C列,也就是查找區域的第2列,所以第三參數為2;
第四參數省略或0,表示模糊查找;
找不到值時會顯示#N/A,所以用函數IFERROR容錯;
IFERROR(表達式,要返回的值),如果表達式是一個錯誤,則返回自己設置的要返回的值,否則返回表達式自身的值,該題中返回空值。
二、函數LOOKUP
公式:G3=IFERROR(LOOKUP(,0/($F$3&ROW(A1)=$B$2:$B$22&COUNTIF(INDIRECT("b2:b"&ROW($2:$22)),$F$3)),C$2:C$22),""),三鍵結束。
三、函數INDEX+SMALL+IF
公式:G3=IFERROR(INDEX(C$2:C$22,SMALL(IF($F$3=$B$2:$B$22,ROW($1:$21),4^8),ROW(A1))),""),三鍵結束。
PS:文章都將以各群友在群中的提問為主打,雖不是最佳的解決方法,但儘可能為提問者提供解題參考,若大家有好的解題方法和建議,歡迎留言,共同交流學習!
作者:仰望~星空
推薦閱讀:
※如何刪除電腦重複圖片?
※速來查找自家財位,財源滾滾來。
※精通 Linux 上的文件搜索
TAG:查找 |