大众情人 如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的年龄。 经典套路: =VLOOKUP(G2,B1:E6,4,0) 万能查询 如下图所示,要根据G2单元格姓名,在A~E数据区域中查询对应的工号。 =LOOKUP(1,0/(G2=B2:B6),A2:A6) 套路指南: =LOOKUP(1,0/(条件区域=指定条件),要返回的区域) 最佳组合 用MATCH函数来定位查询值的位置,再用INDEX函数返回指定区域中指定位置的内容,二者结合,可以实现上下左右全方位的查询。 如下图所示,根据姓名查询部门和职务。 F3单元格公式为: =INDEX(A:A,MATCH($E3,$C:$C,)) 隔壁老王 VLOOKUP函数和MATCH函数结合,常用于不确定列数的数据查询。 如下图所示,要根据B13单元格的姓名,在数据表中查询对应的项目。 C13单元格公式为: =VLOOKUP(B13,A1:G9,MATCH(C12,1:1,),0) 如果数据表的列数非常多,在使用VLOOKUP函数时,还需要掰手指头算算查询的项目在数据表中是第几列,真是麻烦的很。 现在好了,先用MATCH函数来查询项目所在是第几列,然后VLOOKUP函数就根据MATCH函数提供的情报,返回对应列的内容。 多P套路 一对多的查询在日常工作中经常遇到,以下图为例,要提取出财务部的所有人员。 F2单元格输入以下公式后,按Ctrl Shift 回车,然后向下拖动复制即可。 =INDEX(C:C,SMALL((B$2:B$10<>E$2)/1% ROW($2:$10),ROW(B1)))&'' 公式的意思是: 如果B$2:B$10单元格区域中的部门不等于E$2单元格中指定的部门,就除以1%,得到100和0组成的内存数组。 再加上行号ROW($2:$10),如果等于E2单元格姓名,就是0 对应行号,否则就是100 对应行号。 然后使用SMALL函数从小到大依次提取出行号,INDEX函数根据提取出的行号,返回A列对应的内容。 提示:这个公式相对复杂一些,如果暂时看不懂,可以先收藏备用。 图文制作:祝洪忠 |
|