2019年微软正式推出了Xlookup函数,我们可以把它看作是vlookup的升级版。Xlookup弥补了vlookup的诸多不足,可以说是现阶段最强大的查找函数。

但是比较遗憾的是当时仅仅只有office2021、office365才可以使用这个函数,WPS也更新了xlookup函数,我觉得现在是时候来学习下这个强大的查找函数了。

XLOOKUP函数的基本结构是:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

翻译成大白话就是:

=XLOOKUP(查找值,查找范围,结果范围,[找不到时显示的值],[匹配方式],[查询模式])

在这六个参数中,前三个是必须的,后面三个根据自己的需要选择使用。

第一参数(必须参数):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(H2,A1:A9,C1:C9)就能找到正确的结果,在这里我们需要注意的是前三个参数是必须参数,后三个参数是可选参数,也就是说前三个参数必须填写,后三个参数可以选择性填写,在普通查找中,一般将其省略即可。

普通查找.jpg

二、屏蔽错误值

Xlookup函数的第四参数可以屏蔽错误值,这样的话就不必再嵌套IFERROR函数来屏蔽错误值了。

比如在这里我们想要查找孙悟空的语文成绩,在数据表中是没有孙悟空的,函数就会返回#N/A这个错误值,但是我们只需要将公式设置为:=XLOOKUP(H7,A1:A9,C1:C9,"")函数就会将错误值屏蔽掉,在这里两个双引号就表示空值。

如果你将第四参数设置为:"找不到结果"函数的结果就会返回找不到结果

屏蔽错误值.jpg

三、横向查找

在之前想要进行横向查找,就需要使用hlookup这个函数,现在xlookup也具备了横向查找的功能。

需要强调一点的是:第一参数与第二参数的方向与个数都需要一一 对应,否则的话函数也将返回错误值。

四、通配符查找

通过函数介绍可以知道,我们只需要将xlookup的第五参数设置为2就能进行通配符查找,所谓的通配符就是可以代表任意字符的符号,常用的有2个

?:代表任意单个字符

*:代表任意多个字符

使用这个特性,我们可以利用xlookup实现通过关键字查找数据的效果,比如在这里我们将查找值设置为*白,然后只需要将公式设置为:=XLOOKUP(H2,A1:A9,C1:C9,,2)就可以找到李白的语文成绩。

通配符查找.jpg

五、反向查找

所谓的反向查找就是查找左侧的数据,但是Vlookup是不能查找左侧数据的,所以在进行反向查找的时候我们需要使用利用IF{1,0}来构建二维数组,但是xlookup不存在这样的情况,它是可以查找左侧数据的。

比如在这里,我们想要通过工号查找姓名,只需要将函数设置为:=XLOOKUP(H2,B1:B9,A1:A9),就可以找到姓名,非常的简单。

反向查找.jpg

六、多条件查找

Xlookup函数进行多条件查询也非常的简单,只需要利用连接符号将查找值与查找区域连接起来即可

如下图,张飞是存在重名的,在这里我们想要查找2班张飞的语文成绩,只需要将公式设置为:=XLOOKUP(G2&H2,A1:A9&B1:B9,C1:C9)即可找到正确的结果,在这里就是使用连接符号将姓名与班级连接在一起来查找的。

多条件查找.jpg

七、查找多列数据

Xlookup函数可以实现设置一次函数,自动查找多列数据的效果,但是有一个缺点:结果列在数据表中必须是连续的。

如下图所示,我们想要查找他们的成绩,只需要在G2单元格中输入公式:=XLOOKUP(F2,A1:A8,B1:D8)后面的数据就会自动显示,需要注意的是我们需要在G2单元格中向下填充公式.

在这里我们需要明白1点,xlookup返回的结果个数,是由第三参数的列数决定的,在这里我们选择了3列数据,所以函数就会返回3个结果。

八、查找最后一次时间

这个用处不大,主要是为了说明xlookup可以自定义查找方向。Vlookup函数只能从上往下查找数据,而xlookup可以通过设置第六参数实现从下往上查找数据。

我们需要注意的是:当使用xlookup查找数据遇到重复,函数也仅仅只能返回第一个找到的结果,这点与vlookup一致


xlookup函数的作用还有很多,总之就是vlookup能做的它都能做,vlookup不能做的它还能做。