ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 7810|回复: 11

[原创] VLOOKUP实现一对多条件查找

[复制链接]

TA的精华主题

TA的得分主题

发表于 2017-10-10 13:25 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 godfrey0207 于 2017-10-11 15:11 编辑

内网通截图20171010132218.png 输入完公式三键结束,

  1. =IFERROR(SUBSTITUTE(VLOOKUP(K$141&ROW(A1),H$142:I$153&COUNTIF(INDIRECT("H$142:H"&ROW($142:$153)),K$141),2,FALSE),ROW(A1),""),"")
复制代码
解题思路,将符合条件的,通过公式形成一个序列数组,而后和条件区域组合,查找关键词和序列组合,而后用VLOOKUP查找引用


COUNTIF(INDIRECT("H$142:H"&ROW($142:$153)),K$141)这个部分,形成的是一个数组{1;2;3;4;5;5;6;6;7;7;8;8}

欢迎一起讨论,如何取代三剑客,毕竟那个公式,是80年代的,太慢

TA的精华主题

TA的得分主题

 楼主| 发表于 2017-10-10 14:51 | 显示全部楼层
占个沙发,如果亲们觉得有用,欢迎回个帖,如果没用,不用回,哈哈,帖子很快就石沉大海了,哈哈

TA的精华主题

TA的得分主题

发表于 2017-10-10 16:27 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-10-10 16:45 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
可以说是方法之一吧,或者函数嵌套方法之一。
至于速度和秒杀就不好说了哈:)

TA的精华主题

TA的得分主题

发表于 2017-10-10 17:17 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
关于一对多查找,还是觉得你的公式太过抽象,单条件一对多Index优于Vlookup,多条件一对多查找只能用Index,Vlookup或许可以,但太复杂。
a. 单条件一对多查找
Index方式:
要求在E列根据E1的姓名,查找所有该客户消费金额。
E3=Iferror(Index(B:B,Small(If(A$2:A$11=$E$1,Row($2:$11)),Row(A1))),"")
公式说明:
i. IF(A$2:A$11=$E$1,Row($2:$11))
把所有客户名称和E1进行对比,如果相同则返回行号,否则False。
ii. Small(..,Row(a1))
随着公式的向下复制,逐个提取该客户所在的每一个行号数字
iii. Index()
根据行号提取出B列的消费金额
这里出现的Small函数,语法结构为Small(Array,K),k为返回的数据在数组或数据区域里的位置(从小到大)。
Vlookup方式:
要求把张一所有消费金额全列出来
公式:
{=Vlookup(B$9&Row(A1),If({1,0},$B$2:$B$6&Countif(Indirect("B2:B6"&Row($2:$6)),B$9),$C$2:$C$6),2,)}
公式简析:
i. B$9&Row(A1) 连接序号,公式向下复制时会变成B$9连接1,2,3
ii. 给所有的张一进行编号。要想生成编号,就需要生成一个不断扩充的区域(Indirect("B2:B6"&Row($2:$6)),然后在这个逐行扩充的区域内统计“张一”的个数,在连接上$B$2:$B$6后就可对所有的张一进行编号了。
iii. IF({1,0})把编号后的B列和C组重构成一个两列数组
b. 多条件一对多查找
多条件一对多查找,与单条件原理类似,欲设函数实现,R列结果为“$c$10:$c$209<=c2, $e$10:$e$209<=e2, $g$10:$g$209<=g2”的值,并且支持下拉,可如此设置公式。
M10={Index(B:B,Small(If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*($G$10:$G$70<=$G$2),Row($10:$70),4^8),Row(A1)))&""}
公式解析:
i If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*($G$10:$G$70<=$G$2),
Row($10:$70),4^8)
满足这三个条件所有10-70行的数组,不满足情况下为4^8,即Excel2003版最大行数。
ii Small(If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*($G$10:$G$70<=$G$2),
Row($10:$70),4^8),Row(A1))
把上一步计算出的数值区域中寻找第一个符合条件的情况。如果下拉,查找的就是第二个、第三个符合条件之情况。
iii Index(B:B,Small(If(($C$10:$C$70<=$C$2)*($E$10:$E$70<=$E$2)*
($G$10:$G$70<=$G$2),Row($10:$70),4^8),Row(A1)))
列示结果,生成第一个符合条件金额,&“”是为了过滤零值。

TA的精华主题

TA的得分主题

发表于 2017-10-11 14:21 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
楼主这个贴的意思是什么,标题写“提速”,帖子内容里又提“太慢”

如果追求速度,就不要用数组公式,尽量不用COUNTIF、INDIRECT函数,摒弃VLOOKUP

应该考虑使用辅助技术来提高速度

TA的精华主题

TA的得分主题

发表于 2018-4-2 09:27 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-12-15 20:03 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
我只想说: 来人士一辈子,如果不学习excel 真是白来了

TA的精华主题

TA的得分主题

发表于 2019-6-28 08:57 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
好使。但是没能理解透,所以实际运用还不会。

TA的精华主题

TA的得分主题

发表于 2019-6-29 18:40 | 显示全部楼层
用公式时间一长,最后都要走VBA这条路的,只要数据上千就够等的了。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 16:08 , Processed in 0.462709 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表