XLOOKUP已经更新很长时间了,大家使用频率不怎么咋样,这一章给大家盘点盘点XLOOKUP的7个常用方法.
这些模式你都可以直接进行套用.
一、函数语法:
这个函数总共有6个参数,看上去繁多,其实都是实用的功能.
1.前③个参数属于常规用法,非常简单.
2.参数④:相当于内置的IFERROR.
3.参数⑤:可以进行模糊匹配模式.
4.参数⑥:可以进行倒序查找.
二、函数案例解析
我给大家准备了7个用法,分别是:
1.常规查找
2.双层嵌套查找
3.反向查找
4.替换IFERROR查找
5.查找最后一次
6.模糊通配符查找
7.无序近似匹配
这几个用法中第5点和第7点特别值得大家采纳使用.
1.常规查找
用到三个参数,和LOOKUP函数的用法如出一辙.
=XLOOKUP(F3,B2:B17,D2:D17)
2.双层嵌套查找
这个例子需求是根据表头条件和月份条件进行内容的查找,
例如:6月奖金就应该是:271,或者7月目标计划应该是:368
首先用:XLOOKUP(F2,A1:D1,A2:D13)得到表头条件所在的列.
然后把表头条件的列作为最外层XLOOKUP的查找返回区域.
=XLOOKUP(G2,A2:A13,XLOOKUP(F2,A1:D1,A2:D13))
这个做法其实就是替换INDEX搭配MATCH函数的用法.
3.反向查找
左侧的表格是部门人员信息表,G列我想找到D列人员对应的部分.
=XLOOKUP(D2,$B$2:$B$17,$A$2:$A$17)
因为查找区域和返回区域都是分开选择的,所以和正常的查找并没有什么两样.
4.替换IFERROR查找
下面这个表格,我想查找人员"小明"的奖金,但是小明没有出现在人员列中,所以你需要在使用VLOOKUP的情况下,前方加上IFERROR消除错误.
=IFERROR(VLOOKUP(F4,B2:D17,3,0),"NO")
如果你使用XLOOKUP函数,参数④直接可以在函数里面进行消除错误处理.
5.查找最后一次
有一些朋友可能会用LOOKUP(0,1/)这种模式去查找最后一次出现的内容,但是在XLOOKUP函数中间这就是其中的一个参数,你只需要在最后一个参数输入-1,就是倒序/反向查找模式.
=XLOOKUP(F4,C2:C17,D2:D17,,,-1)
查找中级岗位最后一次出现的奖金.
6.模糊通配符查找
下面的表格,我想根据产品进行模糊查找,譬如查找AC,
需要注意的是:如果你要做通配符的模糊查找,需要在参数⑤这个位置进行设置,选择2模式.
=XLOOKUP("*"&E2&"*",A2:A14,C2:C14,,2)
当然,你还可以搭配最后一个参数,找到最后一次出现的AC模糊匹配的销售量.
=XLOOKUP("*"&E2&"*",A2:A14,C2:C14,,2,-1)
7.无序近似匹配
近似匹配,大家经常会用到,
如下面这个例子,
如果销售额在0到10000之间,提成销售额的0%
如果销售额在10000到20000之间,提成销售额的10%
如果销售额在20000到30000之间,提成销售额的20%
如果销售额在30000以上,提成销售额的30%封顶.
用VLOOKUP的基本条件是,查找表格,需要升序排列.然后进行表格的近似查找匹配.
如果查找的表格排序不规则,就会出现错误,如下图所示:
XLOOKUP这一点就比较神了,无所谓你怎么排序,你都可以直接做到内容的近似匹配的无误调取:
=XLOOKUP(B2,$E$4:$E$7,$F$4:$F$7,,-1)*B2
以上
更新不易,大家记得收藏点赞.
谢谢大家的观看!!
如果想要系统学习Excel,可以看看这个链接:
