【原】万般皆套路！Excel中让你爽到爆的查找、求和套路

继续送书！今天送3本《Excel跟卢子一起学 早做完，不加班》，从留言区随机抽奖。昨天中奖名单在文末。

万般皆套路！其实Excel中也有自己的套路。微软帮助曾经把LOOKUP、SUMPRODUCT定义成垃圾函数，而实际上精通这两大函数的套路，99%的查找、求和问题都能解决。

查找之王是LOOKUP函数，而求和之王是SUMPRODUCT函数。

应该很难学吧？

不难！

非常容易理解，分分钟学会，只要记住这个套路就行。

=LOOKUP(1,0/((查找区域1=查找值1)*(查找区域2=查找值2)),返回区域)

=SUMPRODUCT((条件区域1=条件1)*(条件区域2=条件2)*求和区域)

1、根据姓名查找对应的绩效

两个公式很像，都可以针对一个条件，写区域的时候都引用有内容的区域，别引用整列！

=LOOKUP(1,0/($C$2:$C$12=G2),$E$2:$E$12)

=SUMPRODUCT(($C$2:$C$12=G2)*$E$2:$E$12)

2、根据部门、姓名查找对应的绩效

LOOKUP多条件查找的时候，千万别漏了这对括号。

=LOOKUP(1,0/(($B$2:$B$12=G2)*($C$2:$C$12=H2)),$E$2:$E$12)

=SUMPRODUCT(($B$2:$B$12=G2)*($C$2:$C$12=H2)*$E$2:$E$12)

好久以前已经有几百人因为括号问题出错了，详见文章：至今已超过500人出错，LOOKUP函数这对括号问题，你被坑过没？

3、根据部门、姓名为某个姓氏查找对应的绩效

姓氏就是每个姓名的首个字。

LEFT函数就是从左边提取字符。

=LOOKUP(1,0/(($B$2:$B$12=G2)*(LEFT($C$2:$C$12,1)=H2)),$E$2:$E$12)

=SUMPRODUCT(($B$2:$B$12=G2)*(LEFT($C$2:$C$12,1)=H2)*$E$2:$E$12)

4、根据部门、姓名简称查找对应的绩效

经常可以遇到简称和全称的查找，有的人为了贪图方便，记录内容都用简称。

FIND函数就是判断姓名有没出现，出现了就返回数字，否则返回错误值。LOOKUP查找的时候忽略错误值，而SUMPRODUCT不能忽略需要嵌套ISNUMBER判断内容是不是数字。

=LOOKUP(1,0/(($B$2:$B$12=G2)*FIND(H2,$C$2:$C$12)),$E$2:$E$12)

=SUMPRODUCT(($B$2:$B$12=G2)*ISNUMBER(FIND(H2,$C$2:$C$12))*$E$2:$E$12)

5、两者的差别

01 LOOKUP是不管数字还是文本都能查找，而SUMPRODUCT只能查找数字。

LOOKUP根据编号查找所有对应值的效果。

=LOOKUP(1,0/($G2=$A$2:$A$12),B$2:B$12)

SUMPRODUCT根据编号查找所有对应值的效果。

=SUMPRODUCT(($G2=$A$2:$A$12)*B$2:B$12)

02 如果有多个对应值，LOOKUP是查找最后一个，而SUMPRODUCT是对所有数字进行求和。

如根据部门查找绩效。

=LOOKUP(1,0/(G2=$B$2:$B$12),$E$2:$E$12)

=SUMPRODUCT((G2=$B$2:$B$12)*$E$2:$E$12)

这两个函数可以跟其他函数结合，因此变得更加强大。

恭喜这3位粉丝：庆元、姜霓、紫檀，获得书籍《Excel跟卢子一起学 早做完，不加班》，加卢子微信chenxilu2019

VIP 888 元，所有视频课程，终生免费学，提供一年在线答疑服务。

推荐：年轻人都在发EDG，而我却在研究DGET的妙用。。。

上篇：7个好用到强烈推荐的Excel神奇函数，你值得拥有！

你觉得哪个函数最牛？

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）