您現在的位置是:首頁 > 垂釣

不借助任何輔助列,VLOOKUP函式實現一對多查詢技巧

  • 由 Excel自學成才 發表于 垂釣
  • 2022-06-29
簡介不借助輔助列如果製表不允許使用輔助列,要一氣呵成的話,就可以使用公式:在F2中輸入公式:=IFERROR(VLOOKUP($E2&COLUMN(A1),IF({1,0},$B$1:$B$100&COUNTIF(INDIREC

函式可以一對多嗎

舉一個一對多查詢匹配的工作例項,左邊是原始資料,一個部分有對應多個員工,我們現在需要根據部門把這個部門的員工姓名找出來

不借助任何輔助列,VLOOKUP函式實現一對多查詢技巧

藉助輔助列的方法

VLOOKUP函式是一對一查詢的,查詢區域B列如果不是唯一的,那麼插入一個輔助列,輸入的公式是:

=C2&COUNTIFS($C$2:C2,C2)

不借助任何輔助列,VLOOKUP函式實現一對多查詢技巧

這樣做目的是讓A列是保持唯一的,將每個部門後面加上了累計出現的次數

然後使用VLOOKUP公式進行一對多查詢,使用的公式是:

=IFERROR(VLOOKUP($F2&COLUMN(A1),$A:$D,4,0),“”)

不借助任何輔助列,VLOOKUP函式實現一對多查詢技巧

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鍵

不借助任何輔助列,VLOOKUP函式實現一對多查詢技巧

上面的公式看起來很複雜,可以用通用的公式來理解:

=IFERROR(VLOOKUP(查詢值&COLUMN(a1),IF({1,0},查詢列&COUNTIF(INDIRECT(“查詢值列標1:查詢值列標”&ROW($1:$100)),查詢值),結果列),2,0),“”)

那麼這個不用輔助列的一對多查詢,和前面構建輔助列資料思路是一樣的,這裡使用IF函式構建一個虛擬的輔助列陣列。

下次碰到一對多查詢的時候,就不用慌了,今天的技巧用起來即可,你學會了麼?動手研究一下吧~

Top