excel中用vlookup自動(dòng)進(jìn)行一對(duì)多查找函數(shù) _excel

vlookup函數(shù)是excel表格中非常常用的一個(gè)函數(shù)，主要用于返回引用查找值在區(qū)域中的對(duì)應(yīng)數(shù)據(jù)，那通常都是一對(duì)一的查找匹配。
那么在今天的場(chǎng)景中，我們要通過(guò)vlookup函數(shù)來(lái)進(jìn)行一對(duì)多的查找引用。
【excel中用vlookup自動(dòng)進(jìn)行一對(duì)多查找函數(shù)】比如下方數(shù)據(jù)表中，已知客戶(hù)組和各組人員，現(xiàn)在需要使用函數(shù)公式來(lái)提取客戶(hù)組A210的所有人員。

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

文章插圖
這個(gè)公式的作用，實(shí)際上是給客戶(hù)組添加一個(gè)后綴，以使它們變成一個(gè)唯一值，比如A2101,A2102…當(dāng)添加上一個(gè)數(shù)字作為后綴，則變成了列表中不再重復(fù)的一個(gè)文本值。
而countif函數(shù)在這里的用處很關(guān)鍵，它會(huì)返回客戶(hù)組在指定區(qū)域中單元格個(gè)數(shù)，從而得到一個(gè)數(shù)字結(jié)果，并作為后綴與客戶(hù)組的文本連接在一起。
這樣當(dāng)我們?cè)谑褂胿lookup函數(shù)查詢(xún)時(shí)，也可以設(shè)定查找值為"客戶(hù)組+后綴"的形式。
我們先向下填充公式，得到完整的唯一的查詢(xún)列數(shù)據(jù) 。

文章插圖
接著我們?cè)趩卧裰休斎雟lookup函數(shù)公式：VLOOKUP(G4&ROW(A1),$B$2:$D$16,3,0)

文章插圖
公式中第1參數(shù)查找值是G4與row函數(shù)的結(jié)合，row函數(shù)的含義是返回單元格地址的行號(hào) ，其結(jié)果也是一個(gè)數(shù)字，那么G4單元格A210+1（row(a1)的結(jié)果），便得到查找值"A2101" 。
我們?cè)倏吹?參數(shù)的查詢(xún)匹配區(qū)域，首列是通過(guò)公式填充的輔助列，公式會(huì)查詢(xún)第1參數(shù)的值在首列中的位置，然后匹配區(qū)域中指定列數(shù)中的對(duì)應(yīng)單元格。
我們?cè)O(shè)置第3參數(shù)為3，返回引用查詢(xún)匹配區(qū)域中的第3列，即英文名的數(shù)據(jù)列，也就是說(shuō)，公式將執(zhí)行查找A2101在首列中的位置，并返回它在第3列中對(duì)應(yīng)位置的數(shù)據(jù)，從數(shù)據(jù)表中可知A2101對(duì)應(yīng)第3列是"Elizabeth" 。
但這一步還只查詢(xún)到客戶(hù)組A210下的一位人員，我們需要向下填充公式，并組合一個(gè)邏輯函數(shù)的ifeeror來(lái)忽略錯(cuò)誤值。
所以完整的公式為：=IFERROR(VLOOKUP($G$4&ROW(A1),$B$2:$D$16,3,0),"")

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