移动版 - 最新教程 - 热门教程 - 推荐教程 - 热门资源 最专业的Office办公软件学习和原创Office教程网站!

如何用vlookup函数查找返回多个符合条件的结果

发布时间:03-06 来源:兰色幻想-赵志东

最近总有很多同学提问,如何用vlookup函数查找返回多个符合条件的结果,今天本文就再整理一下查找返回多个结果的几种方法。

一、辅助列法

【例1】如下图所示,B列是产品,C列是供应商。要求在下表中,把每个产品的供应商显示在后面列中。

如何用vlookup函数查找返回多个符合条件的结果

分析:利用vlookup的数组公式,可以直接设置公式,但公式很复杂也容易设置错误,所以今天我们只需要添加一个辅助列,问题就可以轻松解决。

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

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

如何用vlookup函数查找返回多个符合条件的结果

有了辅助列,在下表中可以直接设置公式:

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

公式说明:

Column可以让公式向右复制时生成1,2,3..序号,和产品连接后生成产品1,产品2,产品3....。

IFERROR当vlookup查找不到时,显示为空白。

如何用vlookup函数查找返回多个符合条件的结果

补充:很多复杂的公式,都可以通过添加辅助列加以简化,如果你无法设置出复杂的公式,那就试着添加辅助列吧。

二、vlookup数组公式法

【例3】 要求把如图表中所有张一的消费金额全列出来4

如何用vlookup函数查找返回多个符合条件的结果

分析:想实现多项查找,我们可以对查找的内容进行编号,第一个出现的是后面连接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的姓名,查找出所有该客户的消费金额。

如何用vlookup函数查找返回多个符合条件的结果

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() 把公式产生错误值时修改为""(空白)

    图文教程