函数返回引用什么意思_强烈推荐 | XLOOKUP函数太强悍了！完全吊打旧版本的查找引用函数...

全文超过2100字，同时还有5个视频详细讲解XLOOKUP的用法以及和旧版本函数的对比。但是相信我，这些文字和视频非常值得你阅读观看。 文末提供素材和视频下载方式。

众所周知，在Office2019版本出来以前，其它所有旧版本中的VLOOKUP函数绝对是一个明星级的函数。如果你不懂VLOOKUP函数，你都不好意思跟人说你会Excel。

在Office2019版本中，增加的其中一个新函数是XLOOKUP。今天试了一下，实在是太强悍！这一个函数不仅可以取代以前的LOOKUP, VLOOKUP, HLOOKUP, INDEX+,MATCH, 而且功能比这些旧函数更强大，操作更简便。

不夸张地说，XLOOKUP完全是吊打其它查找引用函数。建议一定要看看，动手试一试，而不仅仅是收藏。

PS: 按照官方说法，XLOOKUP函数当前可用于每月频道中的 Microsoft 365 订阅者。 将在2020年7月开始向半年频道中 Microsoft 365 订阅者提供。理论上讲，Office2019每月频道的用户应该也已经更新，装有Office2019的小伙伴可以试一试。

语法

= XLOOKUP （lookup_value，lookup_array，return_array，[if_not_found]，[match_mode]，[search_mode]）

XLOOKUP函数一共有6个参数，前三个为必需参数，后三个为可选参数。看起来比似乎以往的查找函数要复杂。我跟大家理一理。

前三个参数很好理解，其实“找什么，到哪里找，从哪里返回数据”。

第四参数，取代了以前VLOOKUP和IFNA的嵌套使用，意思是，“如果找不到（#N/A）,那么返回指定的替代字符”。

第五参数，如果是模糊匹配的时候找不到，那么选择匹配下一个较小的项还是较大的项？是否使用通配符？这是由这个参数决定的。

第六参数，从前往后搜索查找值还是从后往前搜索查找值？

下面，我们通过几个案例了解XLOOKUP的强悍用法。

案例1 常规查找和逆向

常规查找的方法和VLOOKUP，HLOOKUP很像，但是它既可以按行查找，也可以按列查找。也就是说，以前VLOOKUP和HLOOKUP两个干的活，XLOOKUP都能干。

然而不管是VLOOKUP还是HLOOKUP，被查找区域都只能是一个表数组区域而不是某一行或者某一列，然后指定返回这个区域第几列或者第几行的数据。

而XLOOKUP的用法则灵活得多，查找区域和返回区域是独立的列或者独立的行。更重要的是，被查找的列不需要在返回数据列的左侧！要知道，以前用VLOOKUP进行逆向查找的时候，公式是非常复杂的，需要用到一些比较抽象的技巧，对于初学者很不友好。

而XLOOKUP是轻松解决了这个问题。

XLOOKUP常规查找和逆向查找https://www.zhihu.com/video/1249714914484649984

案例2 返回数组

如果需要用VLOOKUP查找返回多个数据，显然我们需要写入或复制多个公式。或者用MATCH函数充当第三参数，然后复制这个嵌套公式。写的时候还要注意引用方式（相对引用/绝对引用/混合引用）。

在2019版本中，新增了动态数组函数。同时在XLOOKUP其实也可以应用到动态数组的概念了。

也就是说，如果结果返回的是一组数据，那么返回的结果会“自动溢出”到旁边的单元格。“自动溢出”在Excel里面是一个全新的概念。

这样做的好处非常明显：

1. 只需要写入一个函数即可返回多个单元格的结果；
2. 由于返回的数组用了自动溢出的概念，所以写公式的时候不需要考虑单元格引用方式（用相对引用就可以了），公式会显得更简洁。

需要注意的是，被溢出的单元格区域需要保持空值状态，否则会出现#SPILL!错误。

XLOOKUP返回自行溢出数组https://www.zhihu.com/video/1249715031522656256

案例3 模糊匹配

查找数据，免不了会用到模糊匹配。尤其是在查找判断数据区间的时候。

LOOKUP函数在使用的时候默认模糊匹配。但是，用LOOKUP函数进行查找的时候，查找逻辑是用“二分法”，所以要求被查找的数据列必须【升序排列】。如果找不到精确匹配的数据，那么就会返下一个比较小的项（注意了，这是默认规则，不能返回下一个比较大的项哦）。

但是XLOOKUP利用第5参数，可以很简单地返回精确匹配数据或者下一个较小或者下一个较大的项，同时也支持“通配符”。

但是最最最重要的是，用XLOOKUP进行模糊匹配，并不要求被查找数据列进行升序排列！要知道在LOOKUP函数中，这是一个隐藏得很深很容易发生错误的地方。一旦没有升序排列，LOOKUP不会出现错误值，而是依然会返回一个“不可预知”的数据，但是很可能返回的数据是错误的。

单单就这个特性来说，XLOOKUP就已经足够强大了。

XLOOKUP模糊匹配的用法https://www.zhihu.com/video/1249715910606323712

案例4 XLOOKUP嵌套使用取代INDEX+MATCH

在以往版本的查找函数中，有一个经典组合INDEX+MATCH，MATCH函数用于返回行号或者列号，INDEX函数根据返回的行号和列号定位到要返回的数据。

现在，同样的功能，也可以用XLOOKUP函数来实现。通过嵌套XLOOKUP函数，返回指定行指定列的数据。

这个案例中，实现同样的结果，还是XLOOKUP的公式写法更简便。而且，理解XLOOKUP可以返回一组数据在内存中供调用，对于以后再其它函数中嵌套使用也是很有帮助的。

一些嵌套的新用法，有待我们继续挖掘。

XLOOKUP嵌套使用https://www.zhihu.com/video/1249716018479689728

案例5 区间求和/区间计数

再深入一点研究，就会发现XLOOKUP不仅仅能返回单元格中的数据，而且在特定情况下还能返回单元格的名称，比如返回“A1，B2”这样的名称。

这样的话就给了我们更多的想象空间，比如这个案例的区间求和/区间计数。用SUM函数或者COUNT函数和XLOOKUP嵌套使用。

如果不使用XLOOKUP实现区间求和，我们可以利用以往函数进行一些技巧性的组合使用当然也能实现，但是显然公式要复杂得多。

XLOOKUP区间求和与区间计数https://www.zhihu.com/video/1249716101526507520

总结

通过上面的几个案例演示，以及和旧版本函数的对比，相信你已经对XLOOKUP有相当多的了解了。

不得不承认，XLOOKUP确实太强大了！在上面的案例我们可以看出XLOOKUP可以取代旧版本的一些函数，或者大大简化函数公式的写法。

XLOOKUP肯定还有其他方面的用法，需要我们继续挖掘TA的潜能。