在Excel眾多函數當中,VLOOKUP函數估計是出鏡率最高的了,但它似乎有侷限性,這不,今天老闆要在右邊的查詢表中找出部門的所有成員,VLOOKUP好像無能為力?
這個問題,VLOOKUP其實也能輕鬆搞定,且聽技巧君慢慢道來!
文末有示例文件獲取方法哦!
VLOOKUP函數只能找到首個匹配數據
我們知道vlookup總會以第一個被找到數據作為最終的匹配數據,所以,當有四個”市場開發部“同時存在時,很自然的,它只能默認為匹配首個,也就是說只能返回”孫權“
這裡需要注意,VLOOKUP函數只能返回首個匹配數據,這是不可改變的,所以我們可以從數據源入手,把每個部門變成唯一值!
改造數據源生成唯一“編號”是解決問題的基本思路
可以看到,部門人數都不是唯一,我們可以給每個部門的成員一個“編號”,比如財務中心,3個人分別編成“財務中心1”、“財務中心2”...,這樣每名員工就都會有一個唯一的“編號”,把這個“編號”作為查找值即可解決這個問題!
那我們可以把這個問題拆成兩步進行:
- 改造源數據,構造輔助列把部門變成唯一索引值
- 用VLOOKUP函數完成查找匹配
那麼,如何去完成每個部門的編號呢?
用COUNTIF構造輔助列
COUNTIF函數的功能是根據條件計數,在這個問題中恰如其分,
我們用
COUNTIF($C$9:C10,C10)
這樣一個公式來對每個部門出現的次數進行統計,得到每個部門的成員編碼
接著,我們用“&”連接符把部門和編碼組合起來,形成唯一索引,完成數據源的改造:
C10&COUNTIF($C$9:C10,C10)
用VLOOKUP完成查詢
索引列構造完畢,咱們就可以進行查詢了,這裡又有一個問題,查詢表只有一個部門,如何給他加上編碼呢?這裡,咱們可以用ROW函數來構造編碼:
ROW(1:1)
接著,同樣用“&”連接符把部門和編碼組合起來:
$I$10&ROW(1:1)
然後,使用VLOOKUP函數完成查詢:
VLOOKUP($I$10&ROW(1:1),$B$9:$G$24,3,0)
最後,注意到,由於每個部分人數不定,後面會出現錯誤值,我們可以用IFERROR函數來進行美化:
IFERROR(VLOOKUP($I$10&ROW(1:1),$B$9:$G$24,3,0),"")
這樣,使用VLOOKUP進行一對多的重複值查詢就完成啦,你是否學會了呢?
本文示例文件獲取方法:
點擊右上角紅色按鈕 關注EXCEL精選技巧,然後私信發送【VLOOKUP重複值查找】即可獲取
關注EXCEL精選技巧,每天學習3分鐘,堅持一個月,你將大不同!
閱讀更多 Excel精選技巧 的文章