VLOOKUP高能应用:查询符合条件的多个结果

小伙伴们好啊,今天老祝和大家来说说VLOOKUP的高能用法,看看这位大众情人还藏着多少不为人知的秘密。
函数的语法为:
VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)
如下图,需要从B~D的数据表中,根据G1单元格的部门,查询该部门所有的姓名。
VLOOKUP高能应用:查询符合条件的多个结果
首先在A2单元格输入以下公式,向下复制:
=(B2=$G$1)+A1
VLOOKUP高能应用:查询符合条件的多个结果
然后在G5单元格输入以下公式,向下复制:
=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),””)
VLOOKUP高能应用:查询符合条件的多个结果
简单说一下公式的意思:
1、B列的部门每重复出现一次,A列的序号增加1。
2、VLOOKUP函数使用ROW(A1)得到1至N的递增序列作为查询值,在A:C列这个查询区域中,依次返回与递增序号相对应的C列的姓名。
由于VLOOKUP在有多个匹配结果时,默认返回第一条记录,所以只会返回A列中各个序号首次出现的记录。
3、注意查找区域必须由辅助列A列开始。
4、最后将辅助列字体设置为白色或进行隐藏即可。
如果不使用辅助列,VLOOKUP能不能返回符合条件的多个结果呢?
除了公式稍微复杂了些,其实也是可以的。
在G5输入以下公式,向下复制。
=IFERROR(VLOOKUP(G$1,OFFSET(B$1:D$1,MATCH(G4,C:C,0),0,999),2,0),””)
VLOOKUP高能应用:查询符合条件的多个结果
简单说说这个公式的计算过程:

1、MATCH(G4,C:C,0)部分,以G4单元格的内容“姓名”为查找值,在C列中查找该内容所在的位置1。得到的结果作为OFFSET函数向下偏移的行数。
2、OFFSET函数以“B$1:D$1”为基点,根据MATCH函数的查询结果向下偏移1行。向右偏移的列数为0列,新引用的行数是999行,得到的引用区域作为VLOOKUP函数的查询区域。
3、当公式向下复制到G6单元格时,MATCH函数的查询值就变成了G5单元格中的姓名,并返回该姓名在C列中的位置。后面其他单元格中的公式也是如此。
4、OFFSET函数根据MATCH函数得到的位置信息,得到一个新的999行的引用范围。也就是每查询到一个姓名,就从引用区域中将其排除掉。
有了MATCH和OFFSET函数的帮忙,VLOOKUP函数就如鱼得水了。
此公式实现过程有点绕,如果一时难以理解,可以先收藏一下,实际工作中只要会套用就OK了。
好了,今天的分享就是这些,祝小伙伴们一天好心情!练手文件在此:
https://pan.baidu.com/s/1lGiIoEyxmejkMGvP7Vhbag
提取码: 3ha4
图文制作:祝洪忠
本文转自:ExcelHome

注意:站长按照上述两种方法测试发现方法二在人名有重复的时候会出错,有辅助列存在的时候就不会出现错误,建议还是用辅助列的方法,以确保万无一失。

分享到:

评论抢沙发

评论前必须登录!