VLOOKUP函数保姆级教程-1对多查询!

E精精

WPS函数专家

我们进行来学习VLOOKUP函数系列教程,经过前面两节的学习,我们对VLOOKUP的一些基础和进阶用法有了一定的了解!

今天,我们再讲点第二参数重构的案例,现在1对多实现的方法多了,在新函数没出来之前,1对多查询是一个非常麻烦的事情,一般有“INDEX+IF+SMALL万金油讨论”和我们今天要分享的VLOOKUP解法!

01 | 需求说明

下面是模拟的销售部门的组别和组员数据,每个月可能新增和减少,为了方便查看组员情况,现在需要做一个按照组别名称查询组员的模板!

02 | 思路解析

我们都知道VLOOKUP 在查询到首个满足条件的结果后就会返回结果并停止查询,也就是只能返回首个满足条件的结果,那么,多个结果是不是就无解呢?其实,所有的1对多查询都可以转换为1对1查询!

我们给重复的内容编个号,本文中的组别,利用COUNTIF按照出现的顺序编号,然后用内容+编号的方式来出现,就从1对多转成了1对1

03 | 参考解法

注意COUNTIF的第一参数$B$3:B3 冒号前面是绝对引用,下拉区域不断扩大!从而实现按出现的顺序编号!

=B3&COUNTIF($B$3:B3,B3)

有了这个辅助处理,VLOOKUP就可以轻松搞定啦!

VLOOKUP的第一参数注意也要同步添加序号,查询区域也同步变成A:C列而不是B:C列!

=IFNA(VLOOKUP($E$3&ROW(A1),A:C,3,FALSE()),"")

上面的方法简单有效,特别适合版本低的新手朋友,可落地性强!但是有同学可能觉得辅助列不够优雅,能不能一式搞定,哪怕锻炼一下思维也好!想法很好,其实也不难,就是长点!

04 | 进阶写法

只是长了点,不要慌,我们下面会解析的!如果你的版本直接录入不正确,可能还不支持动态数组,可以选择升级版本或者 Ctrl+Shift+Enter 录入公式!

=IFNA(VLOOKUP(ROW(A1),CHOOSE({1,2},COUNTIF(OFFSET($B$3,,,ROW($1:$11)),$E$3),$C$3:$C$13),2,0),"")

下面我们来解析一下,公式的原理,其实核心在第二参数的构建,我们利用OFFSET的高度查询,不断的扩大函数,第一次1行,第二次2行,依次到11行高,通用利用COUNTIF对每个区域中满足查询组别的内容进行计数,从而实现按出现顺序编号的需求,只是这个部分不再需要辅助列!

单独看第二参数,可能更好好理解一些!

到这里,新手可能还是会叹息太难了,没有关系,慢慢积累吧!当然现在你也有更多的处理方法!

05 | 更多解法

只能说时代在发展,公式迭代很快,现在想要实现1对多查询,再也不用烧脑掉头发了,FITLER真想!

护发公式01
=FILTER(C3:C13,B3:B13=E3)

除了FILTER,下面的TOCOL+IF组合也是非常奈斯~

护发公式02
=TOCOL(IF(B3:B13=E3,C3:C13,\),3)

好用的方法这么多了,我们为什么还要和大家交流那么难以理解的VLOOKUP解法呢?其实除了解决问题,很多时候锻炼思路更重要,VLOOKUP第二参数的重构,可以加深我们对数组重构的理解和应用!

OK!VLOOKUP系列教程第四篇就到这里,有什么新函数或者知识点想学习都可以@小编~!

对了~我是 E精精,WPS&Excel深度爱好者!对函数、技巧、VBA、PQ、DAX、JSA、图表、PBI都略有了解!欢迎交流~

江苏省
浏览 5534
9
76
分享
76 +1
42
9 +1
全部评论 42
 
E精精

WPS函数专家

VLOOKUP从入门到精通系列教程 1、基础入门 - https://bbs.wps.cn/topic/16918?chan=share&fromshare=copylink 2、进阶逆向查询的N种写法 - https://bbs.wps.cn/topic/18016?chan=share&fromshare=copylink 3、精通1对多查询 - https://bbs.wps.cn/topic/18031?chan=share&fromshare=copylink 4、模糊匹配也能搞 - https://bbs.wps.cn/topic/18339?chan=share&fromshare=copylink 5、多列结果的各种解法 - https://bbs.wps.cn/topic/18797?chan=share&fromshare=copylink 学完100%有所收获,我是E精精,Excel自动化办公,公众号-【Excel办公实战】号主,2500+原创文章,欢迎一起交流学习!
· 江苏省
1
回复
 
学习中……
· 山东省
回复
 
1
· 河南省
回复
 
打卡一下
· 湖北省
回复
 
打卡
· 北京
回复
 
学习。。。
· 广东省
回复
 
打卡
· 山西省
回复
 
打卡
· 北京
回复
 
学习到了
· 重庆
回复
E精精

WPS函数专家

· 江苏省
回复
 
打卡
· 江苏省
回复
 
感谢
· 云南省
1
回复
 
打卡
· 江苏省
回复
 
打卡
· 浙江省
回复
 
vlookup 确实好用 用惯了xlookup 就不习惯用这个了。
· 湖南省
回复
E精精

WPS函数专家

时代福利 ,后面会有XLOOKUP详解教程!
· 江苏省
回复
 
学习!
· 河南省
回复
 
打卡
· 湖南省
回复
 
这个系列不错
· 河南省
回复
 
打卡
· 上海
回复
 
清华学弟任泽岩

创作者俱乐部成员

置顶了,加油!
· 辽宁省
1
回复
E精精

WPS函数专家

感谢大家的支持!
· 江苏省
1
回复
 
打卡
· 广西
回复