用vlookup函數查找返回多個符合條件的結果的方法
一、輔助列法
【例1】如下圖所示,B列是產品,C列是供應商。要求在下表中,把每個產品的供應商顯示在后面列中。
分析:利用vlookup的數組公式,可以直接設置公式,但公式很復雜也容易設置錯誤,所以今天我們只需要添加一個輔助列,問題就可以輕松解決。
在表格前面插入輔助列和公式:
A2 =B2&COUNTIF(B$1:B2,B2)
有了輔助列,在下表中可以直接設置公式:
=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),"")
公式說明:
Column可以讓公式向右復制時生成1,2,3..序號,和產品連接后生成產品1,產品2,產品3....。
IFERROR當vlookup查找不到時,顯示為空白。
補充:很多復雜的公式,都可以通過添加輔助列加以簡化,如果你無法設置出復雜的公式,那就試著添加輔助列吧。
二、vlookup數組公式法
【例3】 要求把如圖表中所有張一的消費金額全列出來4
分析:想實現多項查找,我們可以對查找的內容進行編號,第一個出現的是后面連接1,第二個出現的連接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,)}
公式說明:
B$9&ROW(A1) 連接序號,公式向下復制時會變成B$9連接1,2,3
給所有的張一進行編號。要想生成編號,就需要生成一個不斷擴充的區(qū)域(INDIRECT("b2:b"&ROW($2:$6)),然后在這個逐行擴充的區(qū)域內統(tǒng)計“張一”的個數,在連接上$B$2:$B$6后就可以對所有的張一進行編號了。
IF({1,0}把編號后的B列和C組重構成一個兩列數組
三、Index+Small函數法
在excel中經常需要用到一對多查找,進而實現篩選功能。vlookup函數可以實現但公式很難理解。這時我們可以使用最常用的index+Small公式組合。
【例】如下圖所示的客戶消費明細表中,要求在E列根據E1的姓名,查找出所有該客戶的消費金額。
E3公式:(數組公式,輸入后按ctrl+shift+enter三鍵結束)
=IFERROR(INDEX(B:B,SMALL(IF(A$2:A$11=$E$1,ROW($2:$11)),ROW(A1))),"")
公式說明:
IF(A$2:A$11=$E$1,ROW($2:$11) 把所有客戶名稱和E1進行對比,如果相同則返回行號,否則值為FALSE。
Small(..,row(a1)) 隨著公式的向下復制,逐個提取該客戶所在的每一個行號數字
Index() 根據行號提取出B列的消費金額
IFERROR() 把公式產生錯誤值時修改為""(空白)
繼續(xù)查找其他問題的答案?
-
Excel 2019如何使用復制、粘貼命令插入Word數據?(視頻講解)
2021-01-257次播放
-
2021-01-2227次播放
-
2021-01-2210次播放
-
2021-01-2212次播放
-
2021-01-226次播放
-
2021-01-223次播放
-
2021-01-222次播放
-
Excel 2019如何利用開始選項卡進入篩選模式?(視頻講解)
2021-01-223次播放
-
2021-01-223次播放
-
2021-01-222次播放
點擊加載更多評論>>