XLOOKUP函数与VLOOKUP函数使⽤⽅法以及实例分析XLOOKUP函数与VLOOKUP函数使⽤⽅法以及实例分析。从9个⽅⾯解读XLOOKUP函数与VLOOKUP/LOOKUP函数之间的
⽤法差异,包括:基础语法、横向查、纵向查、⼀对多、返回多值、反向查、多条件查、模糊查、近似查、匹配最后⼀个等等。还有不明⽩的朋友⼀起详细了解下吧!
1、XLOOKUP基础语法
在学习任何⼀个函数之前,需要了解这个函数的基础语法,从微软官⽅的帮助⽂档⾥到了这个函数的参数语法,共计有5个参数,跟LOOKUP的参数⾮常接近,但是使⽤起来会更加简单了⼀些。
其中第1~3个参数跟LOOKUP的参数⾮常接近,都是将「查区域」和「结果区域」全部独⽴出来了,跟VLOOKUP的「选择区域」就有所差异,拆分出来会让函数更加灵活。
第4个参数match_mode表⽰匹配类型,可以使⽤「精确匹配」「通配符匹配」「2种近似匹配」,默认为0表⽰精确匹配。
第5个参数search_mode是新增的⼀个参数,表⽰搜索模式,在传统的VLOOKUP/LOOKUP系列函数中,搜索只能从上往下,并且寻第2个、最后1个数值的时候会⾮常⿇烦。⽽XLOOKUP就直接引进了这个参数,默认为1表⽰从上往下开始搜索,-1表⽰从下往上搜索,这2个会⽤的⽐较多⼀些。
⾄此,XLOOKUP的语法就初步了解了,接下来来实操下这个XLOOKUP函数有多强⼤!
2、基础纵向查询
例如下图查“⼯号的电脑销售额”,在基础操作上VLOOKUP和XLOOKUP没有什么太⼤的差异,写法都⾮常简单,只是XLOOKUP将选择区域和返回区域拆分出来单独写了⽽已。
来总结下XLOOKUP基础纵向查询的套路:
结论:双⽅平均(VLOOKUP公式会简洁⼀点点,不过差异不⼤)
3、基础横向查询
既然能纵向查询,那么横向查询其实也是可以的。然⽽在VLOOKUP中,如果要实现横向查询会⾮常⿇烦,⼜是需要构建虚拟数组。在这⾥我们使⽤INDEX+MATCH来代替VLOOKUP实现「基础横向查
询」。
⽽使⽤XLOOKUP就⾮常简单了,所有操作都跟纵向查询没有任何差异,XLOOKUP会⾃动识别是什么⽅向,例如将上⾯的数据横放了,变成这个样⼦:
在横向查询中XLOOKUP完爆VLOOKUP,XLOOKUP的两个⽅向查询⽤法⼀模⼀样,没有任何差异,太智能了。
来总结下XLOOKUP横向查询的公式套路:
结论:XLOOKUP完爆VLOOKUP,因为VLOOKUP实现⾮常⿇烦。
4、反向匹配查询
在前⾯的语法中,我们说到XLOOKUP将「查区域」和「结果区域」全部独⽴出来了,所以在这⾥的话,反向匹配对于XLOOKUP来说没有什么障碍,相反VLOOKUP就需要去构建⼀个IF虚拟数组来实现了。
来感受下2个函数的⽤法:
VLOOKUP是通过IF({1,0},XXX,XXX)的⽅式构建⼀个虚拟数组来实现这个功能的,因为检索关键字必须在选择区域的第⼀列,对于新⼿来说理解⾮常不友好,⽽且复杂的数组公式还会消耗⼤量的计算机资源,XLOOKUP依然是这么⽜逼完爆,格式没有什么变化,轻松完成反向查询:
结论:XLOOKUP完爆VLOOKUP,基础语法即可实现。
5、多条件查
多条件查是很多VLOOKUP的初学者的噩梦,需要写⾮常复杂的IF数组公式,⽽且⼜要注意定位引⽤的⽅式,⽽XLOOKUP 使⽤起来就⾮常舒服了,只需要将多个条件利⽤&符号拼接起来就ok。
彻底告别复杂公式:
相信很多同学看到了VLOOKUP多条件查询都是⼀脸懵逼,IF到底是什么⿁,怎么⽼是出现?其实这⾥还是构建了⼀个虚拟数组,数组公式对于新⼿来说理解起来的确太困难。
将绝对定位去掉,我们来看下XLOOKUP公式究竟有多简洁,丝毫不拖泥带⽔:
=XLOOKUP(F4&G4, B4:B17&C4:C17, D4:D17, 0, 1)
总结XLOOKUP多条件查询公式套路:
结论:XLOOKUP再次完爆VLOOKUP,简洁速度快!
6、模糊查询匹配
VLOOKUP和XLOOKUP均⽀持模糊匹配,在Excel中使⽤模糊匹配需要⽤到通配符(*、?、~),这次两个函数不相上
下,XLOOKUP只需要将第4个参数修改成2表⽰通配符匹配即可。
两个函数不相上下,因为这个功能⽐较简单,总结下XLOOKUP的模糊查询公式套路:
结论:XLOOKUP和VLOOKUP不相上下,因为都⽐较基础vlookup模糊匹配
7、匹配最后⼀个值
在某些情况下,我们需要到记录⾥的最后⼀条数据,⽽恰好XLOOKUP的最后⼀个参数是搜索模式,
只要我们将第5个参数search_mode修改成-1,就会倒序查,这样就能到最后⼀个数值了,⾮常简单。
⽽VLOOKUP本⾝实现匹配最后⼀个值⾮常⿇烦,这⾥我们使⽤LOOKUP来代替:
LOOKUP的写法就⾮常难理解了,⼜是⽤0除,⼜是做逻辑符号判断等于的。对于新⼿实在太不友好,相⽐之下XLOOKUP的写法就⾮常简单了。直接将搜索模式⼀改就ok了,这个功能在⼈事应⽤中⾮常⼴,到最后⼀次打卡时间和第⼀次打卡时间,⾮常简单。
套路总结:
结论:XLOOKUP完爆VLOOKUP/LOOKUP,写法简洁,计算快
8、查多个值
查多个值⽆论是利⽤VLOOKUP还是LOOKUP实现起来都⾮常⿇烦,因为需要构建⼀个IF虚拟数组,⽽XLOOKUP就很便捷了。还记得我们的第⼀个参数叫“检索关键字”么?我们只需要将这个参数选中想要查的多个值就ok了。
当然的话,⼀般查多个值会对这些值做⼀个聚合运算,例如到最⼤值、平均值、最⼩值等等。例如下⽅到3个员⼯的销售额最⼤值,就⾮常简单:
⽽VLOOKUP⼜是要构建⼀个⾮常复杂的数组公式,甚⾄还⽤到了T函数,理解起来真是太吃⼒了。总结套路:
结论:XLOOKUP再次完爆VLOOKUP,写法⾮常简单。
9、查返回多列
上⾯⼀个是查多个值,对这个命题扩展下,就可以得到返回多列。在XLOOKUP函数中返回多列套路也⾮常简单,只需要将「结果区域」选择多列就ok。⽽VLOOKUP需要使⽤ROW或者COLUMN函数才能实现。
例如想到某个⼯号的平均销售额,这⾥需要同时返回电脑和⼿机的销售额:
整体来说XLOOKUP函数⽐VLOOKUP函数理解起来更加简洁,因为VLOOKUP函数使⽤了ROW作为辅助函数,对于新⼿来说,理解起来需要⼀定的门槛,总结:
当然的 XLOOKUP函数的⽤法还有⾮常多种,例如还可以通过修改第4个参数实现「近似匹配-包含/不包含最⼩值」,还可以利⽤XLOOKUP函数代替MATCH+INDEX实现筛选功能,甚⾄连Offset这个动态构数函数都可以代替。
以上就是⼩编给⼤家分享的XLOOKUP函数与VLOOKUP函数使⽤⽅法以及实例分析了,希望此篇教程能帮助到你!

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。