Excel函数进阶篇|365新函数XLOOKUP

小伙伴们，今天我们来看看那个传说中的男人（函数）XLOOKUP，好像带X的都比较牛X，比如X-Men，一直是一览众山小、高处不胜寒。自从他的出现，VLOOKUP、HLOOKUP、INDEX+MATCH、OFFSET的查找功能好像没人用了。今天我们就来看看他在查找界到底有啥变种功能。

语法：

XLOOKUP(查找的值,查找范围或数组,返回范围或数组,[找不到返回的值],[查找模式],[搜索模式])

第123是必须，456是可选参数。

我们下面举几个实例来详细说明他的使用方法。

1、单个查找

现有数据区域，需查找姓名对应的性别。

=XLOOKUP(D3,A2:A7,B2:B7)

意思就是查找D3，在哪找，在A2:A7找，返回啥？返回B2:B7对应的值。

2、找不到返回指定的结果

VLOOKUP找不到的时候，会返回错误值，XLOOKUP可以指定找不到时，返回指定结果。

=XLOOKUP(D3,A2:A7,B2:B7,”没这个人”)

在第4个参数设置，如果找不到，返回第4个参数。

3、使用通配符

例如要查找姓张的性别，不可以向VLOOKUP那样，可以直接用通配符来查找，XLOOKUP默认是不支持通配符的，需要在第5个参数来设置，才可以使用。

=XLOOKUP(D3&”*”,A2:A7,B2:B7,”没这个人”,2)

第5个参数可以设置下面4个选项。

0-精确匹配

-1-精确匹配或下一个较小的项

1-精确匹配或下一个较大的项

2-通配符匹配

第5个参数设置成2可以支持通配符查找。

第5个参数0是精确匹配。

4、区间查找

现有数据源销售额，需根据销售额来计算每个人的提成。

=LET(x,XLOOKUP(G3,A2:A7,C2:C7),y,XLOOKUP(x,D2:D6,E2:E6,,-1),x*y)

当第5个参数是-1时，会在第1个数组中查找比给定值小且最接近的数。

5、返回多列查找结果

查找姓名同时返回性别和销售额，不用再像VLOOKUP那样，需要设置第3个参数用COLUMN来动态获取。

=XLOOKUP(D3,A2:A7,B2:C7)

6、从后向前查

第6个参数设置成-1可以实现从后向前查找。

=XLOOKUP(D3,A2:A7,B2:C7,,,-1)

第6个参数可以设置下面4个选项。

1-从第一项到最后一项进行搜索

-1-从最后一项到第一项进行搜索

2-二分搜索(升序排序)

-2-二分搜索(降序排序)

7、横向查找

可以代替HLOOKUP的横向查找。

=XLOOKUP(I2,B1:F1,B2:F2)

8、多条件查询

当有多个条件时，可以直接用&连接，不用需要IF函数再来构建数组。

=XLOOKUP(E3&F3,A2:A7&B2:B7,C2:C7)

9、反向查找

根据姓名查找部门，不用再用IF{1,0}套路了。

=XLOOKUP(E3,B2:B7,A2:A7)

10、交叉查询

就是INDEX+MATCH的交叉查询。

=XLOOKUP($E3,$A2:$A7,XLOOKUP(F2,$B1:$D1,$B2:$D7))

我个人感觉这个还没有INDEX+MATCH来得直观。

好了，今天就聊到这，XLOOKUP函数在平时的工作中也是经常用到的，常规用法也很简单，有兴趣的小伙伴赶紧练起来啊！