VLOOKUP函数在有多个匹配结果时,默认只能返回第一条记录。如果想将多条记录都查询出来,则可以通过辅助列来实现。

如何根据下图中F1单元格的省份,从B~C的数据表中查询出该省份所有的城市。

excel中如何用VLOOKUP匹配多个结果?-趣帮office教程网

一、方法1

1、在A2单元格输入公式:

=(B2=$F$1) A1

并向下复制到A3:A14。当B列的省份每重复出现一次,A列的序号增加1.

2、在F2单元格输入公式:

=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")

并向下复制到F3:F14。使用函数ROW(A1)得到1至N的递增序列作为查询值,在A:C列这个查询区域中,依次返回与递增序号相对应的C列的城市。

用VLOOKUP返回A列中各个序号首次出现的记录。

excel中如何用VLOOKUP匹配多个结果?-趣帮office教程网

二、方法2(使用COUNTIF函数)

1、在A2单元格输入公式:

=COUNTIF($B$2:B2,B2)&B2

并向下复制到A3:A14。计算从B2到当前单元格该省份第几次出现,再将省份与出现的次数组合成文本。

2、在F2单元格输入公式:

=IFERROR(VLOOKUP(ROW(A1)&$F$1,A:C,3,0),"")

并向下复制到F3:F14。使用函数ROW(A1)得到1至N的递增序列与省份组合文本作为查询值,在A:C列这个查询区域中,依次返回与组合文本相对应的C列的城市。

excel中如何用VLOOKUP匹配多个结果?-趣帮office教程网

三、扩展用法

如下图,将列表中各省份涉及的城市都匹配到同一行中。

excel中如何用VLOOKUP匹配多个结果?-趣帮office教程网

1、在A2单元格输入公式:

=COUNTIF($B$2:B2,B2)&B2

并向下复制到A3:A14。

excel中如何用VLOOKUP匹配多个结果?-趣帮office教程网

2、选择B:C数据区域,插入[数据透视表](现有工作表的E1单元格),将[省]拉入行字段,并按升序排列。

3、在G1:K1数据区域分别输入1、2、3、4、5,作为递增序列与省份组合为文本,以此作为查询值,在A:C列这个查询区域中,依次返回与组合文本相对应的C列的城市。

在G1单元格输入公式:

=IFERROR(VLOOKUP(G$1&$E2,$A:$C,3,0)&";","")

并向下、向右复制到G2:K9数据区域。

excel中如何用VLOOKUP匹配多个结果?-趣帮office教程网

4、在F2单元格输入公式:

=G2&H2&I2&J2&K2

并向下复制到F2:F9。将匹配的城市进行拼接,显示到一个单元格内。

excel中如何用VLOOKUP匹配多个结果?-趣帮office教程网