不管是学习什么函数,都先来看看它的用途及语法,参数说明:如下图
XLOOKUP函数出现已经有近一年的时间了,在这个函数的资料刚被爆出来的时候,曾经引起了不小的轰动,很多人宣称这家伙将彻底淘汰VLOOKUP,甚至有人说微软即将把VLOOKUP抛弃了……然而实际情况却并非如此,XLOOKUP函数的存在感非常非常低,最重要的原因是因为这个函数当时只存在于最新的Excel365版本,当时大家使用的其他各个版本的Excel都无法使用该函数。不过现在Excel2021和最新版本的WPS也正式发布了,这就为XLOOKUP函数的普及带来了机会。
这个函数本身是有一点难度的,一共有六个参数,这固然使函数具备了强大功能,但也让一些初学者望而却步。今天就通过一些我们平时常见的案例,来和大家一起了解XLOOKUP的基本用法。
XLOOKUP函数的基本结构是:
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
翻译成大白话就是:
=XLOOKUP(查找值,查找范围,结果范围,[找不到时显示的值],[匹配方式],[查询模式])
在这六个参数中,前三个是必须的,后面三个根据自己的需要选择使用。
案例1:根据姓名查找下面中所对应年龄
直接输入公式
=VLOOKUP(G4,C4:E11,3,0)
LOOKUP函数也可以,是在H4单元格中输入公式:
=LOOKUP(1,0/(C4:C11=G4),E4:E11)
XLOOKUP看了前面两位仁兄的表演后,默默的在H4单元格中写下了公式=Xlookup(G4,C4:E11,E4:E11)。
XLOOKUP第一参数可以是一个值,也可以是一组值
=Xlookup(G4:G6,C4:E11,E4:E11)
就可以批量查找出多个姓名对应的年龄。
案例2:查找姓名中包含“二”的年龄
VLOOKUP函数在面对通配符*和?查找出来的结果是不一样的,因为*是代表对个内容的通配符,而?是代表单个字的通配符,如下图:
案例中的姓名名称有两位和三位不等的存在,当查找通配符带*的时候就会查找到“申德二”对应的年龄,查找通配符带?对应的姓名就是“满二”的年龄。
XLOOKUP函数也可以兼容通配符查找,不过XLOOKUP函数查找的通配符遇到多个结果时返回的是第一个结果值。
案例3:根据姓名从右向左查询部门
VLOOKUP函数反向查找需要使用的IF(1,0),直接在H4单元格中输入公式:
=VLOOKUP(G4,IF({1,0},C4:C11,B4:B11),2,0)
LOOKUP公式:
=LOOKUP(1,0/(C4:C11=G4),B4:B11)
XLOOKUP公式书写出来的效果:
=Xlookup(G4,C4:C11,B4:B11)
案例4:根据部门查找对应人数
第四回合是考验大家横向查找的应变能力,VLOOKUP擅长的是纵向查找,对于横向查找HLOOKUP函数是大家认可的“大师”。
只见HLOOKUP二话没说就在B7单元格中写下公式=HLOOKUP(B6,3:4,2,0)
XLOOKUP公式
=Xlookup(B6,B3:E3,B4:E4)
案例5:根据部门和姓名以及性别查找年龄
提到多条件查找,VLOOKUP函数的公式就显得有些黔驴技穷了:
=VLOOKUP(G4&H4&I4,IF({1,0},B4:B11&C4:C11&D4:D11,E4:E11),2,0)
LOOKUP函数的境况似乎要好些:
=LOOKUP(1,0/(B4:B11=G4)*(C4:C11=H4)*(D4:D11=I4),E4:E11)
XLOOKUP函数见他们都写了好长一串,于是在J4单元格中写下公式:
=Xlookup(G4&H4&I4,B4:B11&C4:C11&D4:D11,E4:E11)
案例6:查找最新日期的产品单价
LOOKUP公式:
=LOOKUP(1,0/(C4:C11=H4),(D4:D11))
Xlookup公式:
=Xlookup(H4,C4:C11,D4:D11,0,-1)
Xlookup作用与参数
Xlookup:搜索数据数据区域中的值,然后返回找到的第一个匹配结果。
语法:=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
第一参数(必须参数):lookup_value,想要查找值
第二参数(必须参数):lookup_array,想要在那个数据区域中查找
第三参数(必须参数):return_array,要返回的数据区域,就是结果所在的区域
第四参数(可选参数):if_not_found,找不到结果,就返回第四参数,如果省略第四参数函数默认返回#N/A这个错误值
第五参数(可选参数):match_mode,指定匹配类型
参数为:0 ,精确匹配,未找到结果,返回 #N/A。这是默认选项。
参数为:-1,近似匹配,未找到结果,返回下一个较小的项。
参数为:1,近似匹配,未找到结果,返回下一个较大的项。
参数为:2 ,通配符匹配
第六参数(可选参数):search_mode, 指定要使用的搜索模式
参数为:1,从第一项开始执行搜索。这是默认选项。
参数为:-1,从最后一项开始执行反向搜索。
参数为:2,根据 lookup_array 按升序排序的二进制搜索。如果未排序,将返回无效结果。
参数为:-2,根据lookup_array 按降序排序的二进制搜索。如果未排序,将返回无效结果。
以上就是xlookup的所有参数,虽然比较多,但是使用起来却非常方便,下面我们就通过实际的例子 来学习下这个函数
一、普通查找
二、屏蔽错误值
Xlookup函数的第四参数可以屏蔽错误值,这样的话就不必再嵌套IFERROR函数来屏蔽错误值了。
如果你将第四参数设置为:"找不到结果"函数的结果就会返回找不到结果
三、横向查找
四、通配符查找
五、反向查找
比如在这里,我们想要通过工号查找姓名,只需要将函数设置为:=XLOOKUP(H2,B1:B9,A1:A9),就可以找到姓名,非常的简单
六、多条件查找
七、查找多列数据
八、查找最后一次时间
我们需要注意的是:当使用xlookup查找数据遇到重复,函数也仅仅只能返回第一个找到的结果,这点与vlookup一致
总的来说,xlookup函数的功能是在vlookup的基础上,更加强大,适应性更强。
页面更新:2024-03-20
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2008-2024 All Rights Reserved. Powered By bs178.com 闽ICP备11008920号-3
闽公网安备35020302034844号