標籤:

一對多查找(2017版)

愛上Excel合伙人工具箱【2017版】99元/個

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:查找 |