利用查找函数LOOKUP“忽略错误值“、”模糊查找“、”返回小于自身值的最大值“、”返回最后一个值”等等特性,我们总结了LOOKUP函数的几种典型用法和灵活用法。
熟练LOOKUP函数的这6种查找套路,一不小心就会成为同事眼中的“高大上”。
接下来,就把这些亮眼的套路分享给大家,并作简短说明。
套路1:正向查找与反向查找
基本公式=lookup(1,0/(条件区域=条件),返回结果区域)
如图,某电器公司销售部4月份销售情况统计。
1)根据销售人员姓名查询销售量(正向查找,由左向右)
G2单元格公式:=LOOKUP(1,0/($B$2:$B$16=F2),$D$2:$D$16)
2)查找销售量最大的销售人员姓名(反向查找,由右向左)
J2单元格公式:=LOOKUP(1,0/(D2:D16=I2),B2:B16)
公式原理:利用查找值“1”,在第二个参数中找到等于或小于“1”的最大值,返回这个值对应的区域位置单元格内容。
在第二个参数中,通过查找区域与查找条件进行对比,符合的则为TURE值(相当于值“1”),不符合的则为FALSE值(相当于值“0”),再用“0”值除以这个数组,只有TURE值可以被“0”相除,其余皆为不能被“0”相除,显示错误值,就会产生一个包含“0”值和若干错误值的数组,而LOOKUP函数特点是,忽略第二参数的错误值,那么“0”值对应的返回区域的值,就是我们要查找的值。
套路2:多条件查找
公式=lookup(1,0/((条件区域1=条件1)*(条件区域2=条件2)),返回结果区域)
如图,查找“销售三部”“何昌辉”的4月份销售业绩
N2单元格公式:=LOOKUP(1,0/((A2:A16=L2)*(B2:B16=M2)),D2:D16)
套路3:模糊查找
公式=lookup(查找内容,查找区域,返回结果区域)
如图,对照销售量等级,推算每位销售人员4月份业绩等级
在E2单元格输入公式:=LOOKUP(D2,$Q$2:$Q$6,$R$2:$R$6),并向下填充复制至E16单元格。
套路4:查找最后一次出现的记录
公式=lookup(1,0/(条件区域=条件),返回结果区域)
如图,查找销售人员“高云”的销售业绩。因为有2个“高云”,LOOKUP只返回最后一次出现符合条件的记录。
套路5:根据简称在全称中查找
公式=lookup(9^9,FIND(简称,全称区域),返回结果区域),其中“9^9”代表一个极大值。
如图,在根据学校简称中查找全称学校总人数
在B2单元格输入公式:=LOOKUP(9^9,FIND(A2,$D$2:$D$11),$E$2:$E$11),向下填充。
套路6:根据全称查找简称
公式=lookup(1,0/FIND(简称区域,全称),返回结果区域)
在I2单元格输入公式:=LOOKUP(1,0/FIND($A$2:$A$7,H2),$A$2:$A$7),并向下填充,即可。