常用Excel查询套路,你用过几个?

大众情人

如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的年龄。

经典套路:

=VLOOKUP(G2,B1:E6,4,0)

常用Excel查询套路,你用过几个?


万能查询

如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的工号。

=LOOKUP(1,0/(G2=B2:B6),A2:A6)

常用Excel查询套路,你用过几个?

套路指南:

=LOOKUP(1,0/(条件区域=指定条件),要返回的区域)


最佳组合

用MATCH函数来定位查询值的位置,再用INDEX函数返回指定区域中指定位置的内容,二者结合,可以实现上下左右全方位的查询。

如下图所示,根据姓名查询部门和职务。

F3单元格公式为:

=INDEX(A:A,MATCH($E3,$C:$C,))

常用Excel查询套路,你用过几个?


隔壁老王

VLOOKUP函数和MATCH函数结合,常用于不确定列数的数据查询。

如下图所示,要根据B13单元格的姓名,在数据表中查询对应的项目。

C13单元格公式为:

=VLOOKUP(B13,A1:G9,MATCH(C12,1:1,),0)

常用Excel查询套路,你用过几个?

如果数据表的列数非常多,在使用VLOOKUP函数时,还需要掰手指头算算查询的项目在数据表中是第几列,真是麻烦的很。


现在好了,先用MATCH函数来查询项目所在是第几列,然后VLOOKUP函数就根据MATCH函数提供的情报,返回对应列的内容。


多P套路

一对多的查询在日常工作中经常遇到,以下图为例,要提取出财务部的所有人员。

F2单元格输入以下公式后,按Ctrl+Shift+回车,然后向下拖动复制即可。

=INDEX(C:C,SMALL((B$2:B$10<>E$2)/1%+ROW($2:$10),ROW(B1)))&””

常用Excel查询套路,你用过几个?


公式的意思是:

如果B$2:B$10单元格区域中的部门不等于E$2单元格中指定的部门,就除以1%,得到100和0组成的内存数组。

再加上行号ROW($2:$10),如果等于E2单元格姓名,就是0+对应行号,否则就是100+对应行号。

然后使用SMALL函数从小到大依次提取出行号,INDEX函数根据提取出的行号,返回A列对应的内容。

提示:这个公式相对复杂一些,如果暂时看不懂,可以先收藏备用。


图文制作:祝洪忠



易学宝微视频教程,1290个Office技巧精粹,每个技巧都与实际工作密切相关。轻松学习技巧,练就职场达人,淘宝搜索关键字:ExcelHome易学宝

本公众号主页回复关键字 大礼包 ,获取50集精彩微视频教程。

始发于微信公众号:Excel之家ExcelHome

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注