位置:首頁(yè) > 軟件操作教程 > 辦公軟件 > Excel > 問(wèn)題詳情

用vlookup函數(shù)查找返回多個(gè)符合條件的結(jié)果

提問(wèn)人:周麗平發(fā)布時(shí)間:2021-07-19

一、輔助列法

【例1】如下圖所示,B列是產(chǎn)品,C列是供應(yīng)商。要求在下表中,把每個(gè)產(chǎn)品的供應(yīng)商顯示在后面列中。

image.png

分析:利用vlookup的數(shù)組公式,可以直接設(shè)置公式,但公式很復(fù)雜也容易設(shè)置錯(cuò)誤,所以今天我們只需要添加一個(gè)輔助列,問(wèn)題就可以輕松解決。

在表格前面插入輔助列和公式:

A2 =B2&COUNTIF(B$1:B2,B2)

image.png

有了輔助列,在下表中可以直接設(shè)置公式:

=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")

公式說(shuō)明:

Column可以讓公式向右復(fù)制時(shí)生成1,2,3..序號(hào),和產(chǎn)品連接后生成產(chǎn)品1,產(chǎn)品2,產(chǎn)品3....。

IFERROR當(dāng)vlookup查找不到時(shí),顯示為空白。

image.png

補(bǔ)充:很多復(fù)雜的公式,都可以通過(guò)添加輔助列加以簡(jiǎn)化,如果你無(wú)法設(shè)置出復(fù)雜的公式,那就試著添加輔助列吧。

二、vlookup數(shù)組公式法

【例3】 要求把如圖表中所有張一的消費(fèi)金額全列出來(lái)4

image.png

分析:想實(shí)現(xiàn)多項(xiàng)查找,我們可以對(duì)查找的內(nèi)容進(jìn)行編號(hào),第一個(gè)出現(xiàn)的是后面連接1,第二個(gè)出現(xiàn)的連接2。。。

公式:

{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}

公式說(shuō)明:

B$9&ROW(A1) 連接序號(hào),公式向下復(fù)制時(shí)會(huì)變成B$9連接1,2,3

給所有的張一進(jìn)行編號(hào)。要想生成編號(hào),就需要生成一個(gè)不斷擴(kuò)充的區(qū)域(INDIRECT("b2:b"&ROW($2:$6)),然后在這個(gè)逐行擴(kuò)充的區(qū)域內(nèi)統(tǒng)計(jì)“張一”的個(gè)數(shù),在連接上$B$2:$B$6后就可以對(duì)所有的張一進(jìn)行編號(hào)了。

IF({1,0}把編號(hào)后的B列和C組重構(gòu)成一個(gè)兩列數(shù)組

三、Index+Small函數(shù)法

在excel中經(jīng)常需要用到一對(duì)多查找,進(jìn)而實(shí)現(xiàn)篩選功能。vlookup函數(shù)可以實(shí)現(xiàn)但公式很難理解。這時(shí)我們可以使用最常用的index+Small公式組合。

【例】如下圖所示的客戶消費(fèi)明細(xì)表中,要求在E列根據(jù)E1的姓名,查找出所有該客戶的消費(fèi)金額。

image.png

E3公式:(數(shù)組公式,輸入后按ctrl+shift+enter三鍵結(jié)束)

=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$11=$E$1,ROW($2:$11)),ROW(A1))),"")

公式說(shuō)明:

IF(A$2:A$11=$E$1,ROW($2:$11) 把所有客戶名稱和E1進(jìn)行對(duì)比,如果相同則返回行號(hào),否則值為FALSE。

Small(..,row(a1)) 隨著公式的向下復(fù)制,逐個(gè)提取該客戶所在的每一個(gè)行號(hào)數(shù)字

Index() 根據(jù)行號(hào)提取出B列的消費(fèi)金額

IFERROR() 把公式產(chǎn)生錯(cuò)誤值時(shí)修改為""(空白)

繼續(xù)查找其他問(wèn)題的答案?

回復(fù)(0)
返回頂部