XLOOKUP函数与VLOOKUP函数用法比较

Excel函数 2023-01-13

本文从9个方面解读XLOOKUP函数与VLOOKUP/LOOKUP函数之间的用法差异，包括：基础语法、横向查找、纵向查找、一对多、返回多值、反向查找、多条件查找、模糊查找、近似查找、匹配最后一个等等。

1、XLOOKUP基础语法

在学习任何一个函数之前，需要了解这个函数的基础语法，从微软官方的帮助文档里找到了这个函数的参数语法，共计有5个参数，跟LOOKUP的参数非常接近，但是使用起来会更加简单了一些。

其中第1~3个参数跟LOOKUP的参数非常接近，都是将「查找区域」和「结果区域」全部独立出来了，跟VLOOKUP的「选择区域」就有所差异，拆分出来会让函数更加灵活。

第4个参数match_mode表示匹配类型，可以使用「精确匹配」「通配符匹配」「2种近似匹配」，默认为0表示精确匹配。

第5个参数search_mode是新增的一个参数，表示搜索模式，在传统的VLOOKUP/LOOKUP系列函数中，搜索只能从上往下，并且寻找第2个、最后1个数值的时候会非常麻烦。而XLOOKUP就直接引进了这个参数，默认为1表示从上往下开始搜索，-1表示从下往上搜索，这2个会用的比较多一些。

至此，XLOOKUP的语法就初步了解了，接下来来实操下这个XLOOKUP函数有多强大!

2、基础纵向查询

例如下图查找“工号的电脑销售额”，在基础操作上VLOOKUP和XLOOKUP没有什么太大的差异，写法都非常简单，只是XLOOKUP将选择区域和返回区域拆分出来单独写了而已。

来总结下XLOOKUP基础纵向查询的套路：

结论：双方平均(VLOOKUP公式会简洁一点点，不过差异不大)

3、基础横向查询

既然能纵向查询，那么横向查询其实也是可以的。然而在VLOOKUP中，如果要实现横向查询会非常麻烦，又是需要构建虚拟数组。在这里我们使用INDEX+MATCH来代替VLOOKUP实现「基础横向查询」。

而使用XLOOKUP就非常简单了，所有操作都跟纵向查询没有任何差异，XLOOKUP会自动识别是什么方向，例如将上面的数据横放了，变成这个样子：

在横向查询中XLOOKUP完爆VLOOKUP，XLOOKUP的两个方向查询用法一模一样，没有任何差异，太智能了。

来总结下XLOOKUP横向查询的公式套路：

结论：XLOOKUP完爆VLOOKUP，因为VLOOKUP实现非常麻烦。

4、反向匹配查询

在前面的语法中，我们说到XLOOKUP将「查找区域」和「结果区域」全部独立出来了，所以在这里的话，反向匹配对于XLOOKUP来说没有什么障碍，相反VLOOKUP就需要去构建一个IF虚拟数组来实现了。

来感受下2个函数的用法：

VLOOKUP是通过IF({1,0},XXX,XXX)的方式构建一个虚拟数组来实现这个功能的，因为检索关键字必须在选择区域的第一列，对于新手来说理解非常不友好，而且复杂的数组公式还会消耗大量的计算机资源，XLOOKUP依然是这么牛逼完爆，格式没有什么变化，轻松完成反向查询：

结论：XLOOKUP完爆VLOOKUP，基础语法即可实现。

5、多条件查找

多条件查找是很多VLOOKUP的初学者的噩梦，需要写非常复杂的IF数组公式，而且又要注意定位引用的方式，而XLOOKUP使用起来就非常舒服了，只需要将多个条件利用&符号拼接起来就ok。

彻底告别复杂公式：

相信很多同学看到了VLOOKUP多条件查询都是一脸懵逼，IF到底是什么鬼，怎么老是出现?其实这里还是构建了一个虚拟数组，数组公式对于新手来说理解起来的确太困难。

将绝对定位去掉，我们来看下XLOOKUP公式究竟有多简洁，丝毫不拖泥带水：

=XLOOKUP(F4&G4, B4:B17&C4:C17, D4:D17, 0, 1)

总结XLOOKUP多条件查询公式套路：

结论：XLOOKUP再次完爆VLOOKUP，简洁速度快!

6、模糊查询匹配

VLOOKUP和XLOOKUP均支持模糊匹配，在Excel中使用模糊匹配需要用到通配符(*、?、~)，这次两个函数不相上下，XLOOKUP只需要将第4个参数修改成2表示通配符匹配即可。

两个函数不相上下，因为这个功能比较简单，总结下XLOOKUP的模糊查询公式套路：

结论：XLOOKUP和VLOOKUP不相上下，因为都比较基础

7、匹配最后一个值

在某些情况下，我们需要找到记录里的最后一条数据，而恰好XLOOKUP的最后一个参数是搜索模式，只要我们将第5个参数search_mode修改成-1，就会倒序查找，这样就能找到最后一个数值了，非常简单。

而VLOOKUP本身实现匹配最后一个值非常麻烦，这里我们使用LOOKUP来代替：

LOOKUP的写法就非常难理解了，又是用0除，又是做逻辑符号判断等于的。对于新手实在太不友好，相比之下XLOOKUP的写法就非常简单了。直接将搜索模式一改就ok了，这个功能在人事应用中非常广，找到最后一次打卡时间和第一次打卡时间，非常简单。

套路总结：

结论：XLOOKUP完爆VLOOKUP/LOOKUP，写法简洁，计算快

8、查找多个值

查找多个值无论是利用VLOOKUP还是LOOKUP实现起来都非常麻烦，因为需要构建一个IF虚拟数组，而XLOOKUP就很便捷了。还记得我们的第一个参数叫“检索关键字”么?我们只需要将这个参数选中想要查找的多个值就ok了。

当然的话，一般查找多个值会对这些值做一个聚合运算，例如找到最大值、平均值、最小值等等。例如下方找到3个员工的销售额最大值，就非常简单：

而VLOOKUP又是要构建一个非常复杂的数组公式，甚至还用到了T函数，理解起来真是太吃力了。总结套路：

结论：XLOOKUP再次完爆VLOOKUP，写法非常简单。

9、查找返回多列

上面一个是查找多个值，对这个命题扩展下，就可以得到返回多列。在XLOOKUP函数中返回多列套路也非常简单，只需要将「结果区域」选择多列就ok。而VLOOKUP需要使用ROW或者COLUMN函数才能实现。

例如想找到某个工号的平均销售额，这里需要同时返回电脑和手机的销售额：

整体来说XLOOKUP函数比VLOOKUP函数理解起来更加简洁，因为VLOOKUP函数使用了ROW作为辅助函数，对于新手来说，理解起来需要一定的门槛，总结：

当然的 XLOOKUP函数的用法还有非常多种，例如还可以通过修改第4个参数实现「近似匹配-包含/不包含最小值」，还可以利用XLOOKUP函数代替MATCH+INDEX实现筛选功能，甚至连Offset这个动态构数函数都可以代替。

上一篇：excel-如何创建公式并使用内置函数执行计算和解决问题下一篇：Excel函数的构成、参数及其说明

XLOOKUP函数与VLOOKUP函数用法比较

热门文章

最新文章

标签列表