点击 👆 office研习社 👇 关注星标 ★ 不迷路
你好,我是小智。
今天跟大家分享XLOOKUP函数。
XLOOKUP是Office365中的新函数,能力特别强大。
就连Excel的明星VLOOKUP在它面前都要黯然失色。
那么他究竟有哪些强大之处呢? 让我们一起来学习吧。
1、普通查找
XLOOKUP函数是一个查找函数。
它的语法是 XLOOKUP(查找值,查找数组,返回数组,未找到值,匹配模式,搜索模式),一共六个参数。
=XLOOKUP(lookup_value,lookup_array,return_array, [if_not_found], [match_mode], [search_mode])
如下图所示,要根据G3单元格中的姓名,在左侧数据区域中查询所属部门。
H3单元格公式为:
=XLOOKUP(H2,B2:B11,C2:C11)
公式解析:
公式中的H2,是需要查询的姓名,B2:B11是姓名所在的区域,C2:C11就是我们要回传信息的区域。
我们还可以利用Excel2021的自动溢出功能,同时获取部门和职务的信息。
使用公式如下:
=XLOOKUP(G3,B2:B11,C2:D11)
2、多条件查找
XLOOKUP函数支持多条件查找。
Xlookup函数进行多条件查询非常简单,只需要利用连接符号将查找值与查找区域连接起来即可。
如下图所示, 我们要根据G3和H3的姓名以及部门来查询对应的职务, 可以使用以下公式来实现:
=XLOOKUP(G3&H3,B2:B11&C2:C11,D2:D11)
如果公式找不到内容时,我们可以用第四参数来指定找不到内容时的返回值。
比如下图:
使用公式: =XLOOKUP(G3,B2:B11,C2:C11,"找不到")
当我们需要查找的姓名在B列单元格内找不到对应的内容时,返回的值为「找不到」。
3、指定匹配类型
在我们查询数值的时候,可以使用第五参数来指定使用匹配方式。
第五参数:指定匹配类型
参数为:0 ,精确匹配,未找到结果,返回 #N/A。这是默认选项。
参数为:-1,近似匹配,未找到结果,返回下一个较小的项。
参数为:1,近似匹配,未找到结果,返回下一个较大的项。
参数为:2 ,通配符匹配
如下图,要根据右侧的对照表,将E2单元格中的考核分变成对应的等级, 可以使用以下公式:
=XLOOKUP(E2,H:H,I:I,,-1)
我们使用的是近似匹配,所以这里的第四参数可以直接省略。
我们还可以使用通配符来实现关键字的查询,参数写2,公式如下:
=XLOOKUP(H8&"*",C2:C12,B2:B12,,2)
4、返回第一个或最后一个结果
如下图所示,B列有两个后勤部的记录。
这种查询区域有多个的情况,我们可以用第六参数来指定返回的是第一个还是最后一个。
这里我们想返回第一个记录, 使用如下公式:
=XLOOKUP(G2,C2:C12,B2:B12,"找不到",,1)
这里的第六参数也可以省略,默认为第一个记录。
如果想返回最后一个记录,第六参数写「-1」就可以了。
公式如下:
=XLOOKUP(G2,C2:C12,B2:B12,"找不到",,-1)
5、查询二维表内数据
XLOOKUP函数还可以从二维表里查询获取数据。
下图是下半年各部门每月评分,要根据I1单元格的部门和I2单元格的月份,从左侧表格中查询对应的数值。
我们可以使用如下公式:
=XLOOKUP(I1,A2:A8,XLOOKUP(I2,B1:G1,B2:G8))
这个公式里我们使用了两个XLOOKUP函数嵌套。
先来看里面的XLOOKUP(I2,B1:G1,B2:G8)部分。目的是根据I2单元格的月份,在B1:G1单元格区域中查询到该月份,然后返回B2:G8单元格对应的内容。
可以得到九月列下的所有数据。
外层的XLOOKUP函数,查询值为I1单元格的部门,以A列作为查询区域,以内层XLOOKUP返回的结果作为回传区域,最后在二维表中返回结果。
通过这几个实例,你有没有体会到XLOOKUP的强大呢。功能强大,操作灵活,而且很简单,没有不用它的理由。
以上就是今天分享的干货技巧,你Get到了吗?别忘记动手练习鸭~
office研习社,每天陪你学习一点点,进步一点点。
喜欢今天的文章,别忘记 「收藏」 点 「在看」 支持~
咱们下期再见!^_^
— END —
作者:细姐,Excel效率达人,用通俗易懂的方式帮你解决Excel问题。来源:office研习社(ID:office_360)关注我,提高工作效率早下班。