HELLO大家好！今天和大家分享VLOOKUP函數一對多查找技巧。

會使用EXCEL函數公式的小夥伴一定知道，VLOOKUP函數可以根據查找值在查找區域内找到并返回第一次出現的内容相對應的數據，但是如果一個查找值對應多條數據，那麼該如何查找并返回多條對應的數據呢？

如圖，通過查找财務部，返回财務部所有的員工姓名。

看似這個問題很複雜，其實我們隻需在數據源前面加一個輔助列就可以用VLOOKUP輕松搞定一對多查找。

如圖，在數據源前面插入一列，在A2輸入公式=COUNTIF(B$2:B2,$F$2) 向下填充公式，統計B$2:B2，B$2:B3，B$2:B4，B$2:B5……單元格中，F2單元格中内容的次數，通過動态區域條件計數，凡是和F2單元格相同内容形成了一個123……依次往下的編号，而且B列内容和F2單元格内容相同對應的編号數字從上往下都是第一次出現的。

有了輔助列的編号之後，我們發現原來查找的财務部現在隻需要查找123就可以返回相對應的數據了，那麼這樣的問題就太簡單了，我們隻需用一個ROW(A1)作為VLOOKUP第一參數，公式下拉，ROW(A1)，ROW(A2)，ROW(A3)……也就是查找1，查找2，查找3……，公式=VLOOKUP(ROW(A1),A:D,3,FALSE)

公式向下填充之後，我們發現超出部門相對應人數時，公式計算返回錯誤值，那我們再嵌套一個屏蔽錯誤值的函數就可以完成美化表格了，公式=IFNA(VLOOKUP(ROW(A1),A:D,3,FALSE),"")

以上介紹的是借助輔助列完成VLOOKUP一對多查找的方法，那麼能不能不添加輔助列，直接用函數公式引用數據區域寫VLOOKUP一對多查找呢？

當然可以，俗話說一個籬笆三個樁，一個好漢三個幫，隻需給他幾個好幫手，VLOOKUP就可以直接對數據源實現一對多查找了。

公式=IFNA(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17),2,FALSE),"")

公式淺析

INDIRECT("B2:B"&ROW($2:$17)) 依次對B2:B2，B2:B3，B2:B4，B2:B5……B2:B17的引用

COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2) 依次統計B2:B17區域中F2單元格内容的數量，形成一個和原來輔助列相同的編号

然後再使用IF{1,0}水平數組可以垂直擴展的原理，将數據源整合成一個2列16行的一維數組

IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17)

有了這樣一個直觀的2列16行一維數組，我們可以在腦海中形成一個公式的内存數組，VLOOKUP第一參數還是查找ROW(A1)，然後将這部分内存數組作為VLOOKUP第二參數，那麼這裡隻有兩列數據，第三參數返回的列序号也自然需要寫2了，第四參數精确查找，寫FALSE。

VLOOKUP(ROW(A1),IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17),2,FALSE)

當然，這個公式下拉當超過要查找值對應數量時還是會出現錯誤值，那我們還是用IFNA函數來屏蔽錯誤值。

這個公式是數組公式，輸入公式别忘了按CTRL SHIFT ENTER

=IFNA(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17),2,FALSE),"")

寫函數公式，隻需有了好的思路，我們需要處理的表格問題也就變得簡單了，關注表哥學習更多OFFICE技術。

圖文作者：路表哥

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!