数据查找新时代来临!VLOOKUP完美谢幕,介绍一下强大的继任者XLOOKUP!

编按

XLOOKUP函数出现已经有近一年的时间了,在这个函数的资料刚被爆出来的时候,曾经引起了不小的轰动,很多人宣称这家伙将彻底淘汰VLOOKUP,甚至有人说微软即将把VLOOKUP抛弃了……然而实际情况却并非如此,XLOOKUP函数的存在感非常非常低,最重要的原因是因为这个函数当时只存在于最新的Excel365版本,当时大家使用的其他各个版本的Excel都无法使用该函数。不过现在Excel2021版本也正式发布了,这就为XLOOKUP函数的普及带来了机会。

这个函数本身是有一点难度的,一共有六个参数,这固然使函数具备了强大功能,但也让一些初学者望而却步。今天就通过一些我们平时常见的案例,来和大家一起了解XLOOKUP的基本用法。

HLOOKUP函数的基本结构是:

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

翻译成大白话就是:

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

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

示例1:常规匹配(对标LOOKUP)

按照姓名匹配入职日期,公式为=XLOOKUP(G2,B:B,C:C)。

这种用法和LOOKUP的使用结构非常像,但是有本质的区别。LOOKUP函数要求查找范围必须升序排列,而XLOOKUP则无此限制。就这个问题使用LOOKUP的结果如图所示:

关于LOOKUP的用法不在本文讨论范围,请参阅公众号之前的相关教程。

同事打死不愿说,到底这Excel函数有什么玄机?我看完才懂!

再比如按姓名匹配员工ID,这在VLOOKUP的用法中叫反向查找,XLOOKUP还是一样的用法,公式为=XLOOKUP(G2,B:B,A:A)。

这是给大家介绍的第一种用法,XLOOKUP(查找值,查找范围,结果范围),没有顺序要求,没有方向要求,这体验一下子就超过了VLOOKUP和LOOKUP。

示例2:一次查找多个值(数组用法)

如果XLOOKUP的第一参数选择一个单元格区域,可以对应得到多个结果。在Excel365中更容易看到这种数组自动扩展的效果。

在此提醒那些用VLOOKUP时,第一参数习惯选一列的朋友,如果你这样用XLOOKUP的话,电脑能卡死!

XLOOKUP的这种特性非常重要,比如要统计某几个人的岗位津贴总和,就可以直接用公式=SUM(XLOOKUP(F2:F5,A:A,D:D))得到结果,这个公式非365用户需要按Ctrl+Shift+Enter三键。

示例3:第四参数的妙用

来看这个例子,按照姓名找对应的成绩,当出现数据源中不存在的姓名是,结果为#N/A。

通常遇到这种情况我们的第一反应是外面嵌套一个IFERROR函数,实际上XLOOKUP的第四参数就可以取代IFERROR函数了。

公式修改为=XLOOKUP(D2,A:A,B:B,"姓名有误")。

对于这个参数,我想大家都很容易掌握,毕竟使用一个参数就能少嵌套一个函数,这是非常好的体验。

示例4:多样的匹配方式

XLOOKUP提供了四种匹配方式。

从函数自带的提示不难看出四种匹配方式的意思。0或者省略是精确匹配,之前的例子都是这种方式。-1是精确匹配或下一个较小的项,例如按照成绩匹配等级,可以使用公式=XLOOKUP(B2,F:F,G:G,"",-1)。

这个公式的意思是在F列中找52,找不到的时候就找小于52的一个值,也就是0,最后得到的结果就是0所对应的等级。如果就这样看的话,似乎用LOOKUP更简单。

但是LOOKUP要求查找范围升序,假如数据变成这样,结果就全错了。

可以看出XLOOKUP函数完全不受顺序的影响,LOOKUP则多了一些限制。

如果匹配方式用1的话则正好相反,找不到要找的值时,则会找较大的一个值。例如公式=XLOOKUP(B2,F:F,G:G,"",1)就会得到这样的结果。

从这个例子可以看出,在做区间匹配时,-1对应下限值,1对应上限值。

示例5:使用通配符

有时候在匹配数据时会用到通配符,例如根据单位检查匹配对应的业务人员,公式为=XLOOKUP("*"&D2&"*",A:A,B:B,"无对应人员",2)。

XLOOKUP函数默认不支持通配符的,如果要用通配符,第五参数必须填2,这也是XLOOKUP函数的一个特殊之处。

示例6:多种查询方式

查询方式和匹配方式是不一样的概念,XLOOKUP提供了四种查询方式:

查询方式1是从上向下,默认的也是这种方式。查询方式-1是从下向上,如果要查找的值是唯一的,那么这两种方式得到的结果是一样的,但是当要查找的值有多个的时,两种方式的区别就出现了。

例如公式=XLOOKUP(E2,B:B,C:C,"",0,1)得到的就是每个人的首日销量。

而公式=XLOOKUP(E2,B:B,C:C,"",0,-1)得到的则是每个人的末日销量。

这两种查询方式都是遍历法原理,只是查询方向的区别,而查询方式2和-2,则用的是二分法原理,区别就是二分法的时候默认升序还是降序。关于遍历法和二分法原理,理论性较强,可以参考之前的这篇教程,本文就不赘述了。久等!VLOOKUP坐字法合并单元格查找的秘密终于破了!

示例7:横向匹配和多列匹配

横向查找之前多是用HLOOKUP函数来解决,现在也可以用XLOOKUP,只要查找范围和结果范围是横向的就行。例如公式=XLOOKUP(B6,1:1,2:2)就是横向查找的结果。

在没有XLOOKUP的时候,我们使用VLOOKUP做多列匹配往往要用到COLUMN函数,现在就方便了,只要将结果区域选择多列即可,注意这种用法只能对连续的多列匹配适用。

以上就是XLOOKUP函数的基本用法,功能确实很多也很强大,希望有条件的伙伴能够赶紧练起来,也许在你使用的过程中有什么心得也欢迎留言和大家分享。

阅读推荐

关注我们,发现更多Excel优质教程

比VBA好用100倍!拆分工作表,用数据透视表5秒就搞定!

靠一只“猪”一秒拆分上千个工作表?!同事的骚操作看呆我......

不懂这个“人类高质量Excel技巧”,就不要轻易在简历上写“精通Excel”

大厂HR:有这种Excel思维的实习生,我真的想高薪留下来

小瞧日期函数的都被打脸了!同事用这个函数做Excel时间表,效率提高35%!

(0)

相关推荐