您現在的位置是:首頁 > 垂釣
不借助任何輔助列,VLOOKUP函式實現一對多查詢技巧
- 由 Excel自學成才 發表于 垂釣
- 2022-06-29
函式可以一對多嗎
舉一個一對多查詢匹配的工作例項,左邊是原始資料,一個部分有對應多個員工,我們現在需要根據部門把這個部門的員工姓名找出來
藉助輔助列的方法
VLOOKUP函式是一對一查詢的,查詢區域B列如果不是唯一的,那麼插入一個輔助列,輸入的公式是:
=C2&COUNTIFS($C$2:C2,C2)
這樣做目的是讓A列是保持唯一的,將每個部門後面加上了累計出現的次數
然後使用VLOOKUP公式進行一對多查詢,使用的公式是:
=IFERROR(VLOOKUP($F2&COLUMN(A1),$A:$D,4,0),“”)
VLOOKUP函式第1個引數使用F2&COLUMN(A1),表示市場1,向右填充,就是查詢市場2,市場3。。。。
用IFERROR來遮蔽錯誤值,當有錯誤值時顯示為空白。
不借助輔助列
如果製表不允許使用輔助列,要一氣呵成的話,就可以使用公式:
在F2中輸入公式:
=IFERROR(VLOOKUP($E2&COLUMN(A1),IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT(“b1:b”&ROW($1:$100)),$E2),$C$1:$C$100),2,0),“”)
輸入完按CTRL+shift+enter鍵
上面的公式看起來很複雜,可以用通用的公式來理解:
=IFERROR(VLOOKUP(查詢值&COLUMN(a1),IF({1,0},查詢列&COUNTIF(INDIRECT(“查詢值列標1:查詢值列標”&ROW($1:$100)),查詢值),結果列),2,0),“”)
那麼這個不用輔助列的一對多查詢,和前面構建輔助列資料思路是一樣的,這裡使用IF函式構建一個虛擬的輔助列陣列。
下次碰到一對多查詢的時候,就不用慌了,今天的技巧用起來即可,你學會了麼?動手研究一下吧~