1. 首頁 > 生活百科 > >

excel中用vlookup自動進行一對多查找函數

excel生活經驗

vlookup函數是excel表格中非常常用的一個函數，主要用于返回引用查找值在區域中的對應數據，那通常都是一對一的查找匹配 。
那么在今天的場景中，我們要通過vlookup函數來進行一對多的查找引用 。
【excel中用vlookup自動進行一對多查找函數】比如下方數據表中，已知客戶組和各組人員，現在需要使用函數公式來提取客戶組A210的所有人員 。

excel中用vlookup自動進行一對多查找函數

文章插圖
一對多查找引用，一直以來都是excel中較為復雜的一類知識 ， 但其實它的解法并不復雜，尤其隨著excel版本的更新，也出現了例如FILTER等功能強大的函數 ， 來快速搞定一對多的查詢匹配 。
但今天我們還是來介紹一下vlookup函數在一對多場景中的使用方法 。
首先，我們創建一個輔助列，并在單元格中輸入公式：=C2&COUNTIF($C$2:C2,C2)
excel中用vlookup自動進行一對多查找函數

文章插圖
這個公式的作用，實際上是給客戶組添加一個后綴，以使它們變成一個唯一值，比如A2101,A2102…當添加上一個數字作為后綴，則變成了列表中不再重復的一個文本值 。
而countif函數在這里的用處很關鍵，它會返回客戶組在指定區域中單元格個數，從而得到一個數字結果，并作為后綴與客戶組的文本連接在一起 。
這樣當我們在使用vlookup函數查詢時，也可以設定查找值為"客戶組+后綴"的形式 。
我們先向下填充公式，得到完整的唯一的查詢列數據 。
excel中用vlookup自動進行一對多查找函數

文章插圖
接著我們在單元格中輸入vlookup函數公式：VLOOKUP(G4&ROW(A1),$B$2:$D$16,3,0)
excel中用vlookup自動進行一對多查找函數

文章插圖
公式中第1參數查找值是G4與row函數的結合，row函數的含義是返回單元格地址的行號 ， 其結果也是一個數字，那么G4單元格A210+1（row(a1)的結果），便得到查找值"A2101" 。
我們再看第2參數的查詢匹配區域，首列是通過公式填充的輔助列，公式會查詢第1參數的值在首列中的位置，然后匹配區域中指定列數中的對應單元格 。
我們設置第3參數為3，返回引用查詢匹配區域中的第3列，即英文名的數據列 ， 也就是說，公式將執行查找A2101在首列中的位置，并返回它在第3列中對應位置的數據，從數據表中可知A2101對應第3列是"Elizabeth" 。
但這一步還只查詢到客戶組A210下的一位人員，我們需要向下填充公式 ， 并組合一個邏輯函數的ifeeror來忽略錯誤值 。
所以完整的公式為：=IFERROR(VLOOKUP($G$4&ROW(A1),$B$2:$D$16,3,0),"")
excel中用vlookup自動進行一對多查找函數

文章插圖
這里我們需要注意的是，由于要下拉填充公式 ， 因此要記住將第1參數中的G4進行絕對引用；而row函數進行下拉，其結果會自動更新，得到不同數值結果 ， 而使查找值處于不重復的狀態中，來查詢匹配首列中相同的數據 。
最后當G4連接row函數的數值結果無法與輔助列中的數據相匹配，比如G4&row(a5) ， 結果為A2105,在輔助列中沒有數據可以匹配上，因此公式的計算結果會出錯，這時iferror函數的作用便體現出來 ， 而返回一個空值，看上去則是一個沒有數據的空白單元格 。
最后我們來總結一下，一對多查找的關鍵是靈活設置查找值 ， 通過創建輔助列，得到一個唯一的數據列，然后將它作為首列進行查詢匹配 。之后再使用row函數來自動更新查找值 ， 從而得到一個關鍵字下的多個結果 。