查找、求和、最大一次全搞定,这简直就是万能的套路!
学员的问题,从不同系统导出来的单号两边是不一样,左边是单个,右边是多个,现在想将右边对应的金额、最后日期引用过来。
相当于字符多的引用字符少的,关于这个,卢子早期分享过一个通用的查找套路。
早期案例回顾:左边的是一到四级的科目合并在一起,右边的是四级科目。
=LOOKUP(1,0/FIND($E$3:$E$65,B3),$F$3:$F$65)
语法说明:
=LOOKUP(1,0/FIND(字符少的,字符多的),返回区域)
现在不过就是科目变成了单号,仅此而已。假如现在只对应一个金额,就可以直接套用进去。
=LOOKUP(1,0/FIND($B$2:$B$6,F2),$C$2:$C$6)
不过,这里有所不同,右边的单号是对应着多个值的,需要将所有金额求和。LOOKUP是查找,求和就是SUMPRODUCT。按着语法套进去,发现结果是错误值,怎么回事呢?
LOOKUP是一个特殊的函数,可以忽略错误值,而SUMPRODUCT、SUM、MAX等系列函数是不支持的,错误值运算就出错。
FIND找不到就是错误值,可以嵌套ISNUMBER。
=SUMPRODUCT(ISNUMBER(FIND($B$2:$B$6,F2))*$C$2:$C$6)
这里也可以用SUM函数的数组公式,也就是按Ctrl+Shift+Enter结束。
=SUM(ISNUMBER(FIND($B$2:$B$6,F2))*$C$2:$C$6)
同理,最后日期也就是最大的日期,将SUM换成MAX并修改区域,再将单元格设置为日期格式即可。
=MAX(ISNUMBER(FIND($B$2:$B$6,F2))*$A$2:$A$6)
有了这个套路以后,再也不用害怕简称、全称核对了。
推荐:LOOKUP函数的妙用!
你还知道什么好用的套路?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
赞 (0)