其他
XLOOKUP和LOOKUP,谁才是真正的Excel查找之王?
与 30万 粉丝一起学Excel
隔三差五的就有粉丝给卢子留言,吹新函数XLOOKUP牛逼,多么厉害,事实真的如此吗?口说无凭,还是通过实际案例来证明吧。
1.根据序号查找姓名
=XLOOKUP(H2,$A$2:$A$8,$C$2:$C$8)
=LOOKUP(1,0/(H2=$A$2:$A$8),$C$2:$C$8)
基本语法有点类似,核心的内容几乎一样。
=XLOOKUP(查找值,查找区域,返回区域)
=LOOKUP(1,0/(查找值=查找区域),返回区域)
2.反向查找,根据姓名查找所属部门
=XLOOKUP(H2,$C$2:$C$8,$B$2:$B$8)=LOOKUP(1,0/(H2=$C$2:$C$8),$B$2:$B$8)
3.横向查找,根据基础工资、应发合计,查找对应的金额
=XLOOKUP(I$1,$D$1:$F$1,$D$2:$F$2)=LOOKUP(1,0/(I$1=$D$1:$F$1),$D$2:$F$2)
4.多列多行查找,根据姓名,按顺序查找对应的3列金额
=XLOOKUP($H2,$C$2:$C$8,D$2:D$8)=LOOKUP(1,0/($H2=$C$2:$C$8),D$2:D$8)
5.多条件查找,根据所属部门、姓名查找应发合计
=XLOOKUP(H2&I2,$B$2:$B$8&$C$2:$C$8,$F$2:$F$8)=LOOKUP(1,0/(H2&I2=$B$2:$B$8&$C$2:$C$8),$F$2:$F$8)
6.找不到对应值的处理,多条件查找,根据所属部门、姓名查找应发合计
=XLOOKUP(H2&I2,$B$2:$B$8&$C$2:$C$8,$F$2:$F$8,"")=IFERROR(LOOKUP(1,0/(H2&I2=$B$2:$B$8&$C$2:$C$8),$F$2:$F$8),"")
=XLOOKUP(查找值,查找区域,返回区域,"错误值显示值")
=IFERROR(LOOKUP(1,0/(查找值=查找区域),返回区域),"错误值显示值")
XLOOKUP在这里略有优势。
7.查找所属部门,最后一次出现的姓名
=XLOOKUP(E2,$B$2:$B$8,$C$2:$C$8,,,-1)=LOOKUP(1,0/(E2=$B$2:$B$8),$C$2:$C$8)
8.按绩效判断对应区间的等级
=XLOOKUP(E2,$A$2:$A$5,$C$2:$C$5,,-1)=LOOKUP(E2,$A$2:$A$5,$C$2:$C$5)
9.根据简称查找全称的对应值
=XLOOKUP("*"&D2&"*",$A$2:$A$3,$B$2:$B$3,,2)=LOOKUP(1,0/FIND(D2,$A$2:$A$3),$B$2:$B$3)