如何用vlookup函數查詢返回多個符合條件的結果

最近總有很多同學提問，如何用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

給所有的張一進行編號。要想生成編號，就需要生成一個不斷擴充的區域(INDIRECT("b2:b"&ROW($2:$6))，然後在這個逐行擴充的區域內統計“張一”的個數，在連線上$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() 把公式產生錯誤值時修改為""(空白)