本文详细介绍了Excel新引入的XLOOKUP函数，它能替代VLOOKUP、HLOOKUP、LOOKUP等多个旧版函数，提供更强大、简便的查找和引用功能。XLOOKUP支持常规查找、逆向查找、模糊匹配、返回数组等多种用法，并且在模糊匹配时无需数据列升序排列。此外，XLOOKUP还可以应用于动态数组，实现单个公式返回多单元格结果。文章通过多个案例展示了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是轻松解决了这个问题。
案例2 返回数组
如果需要用VLOOKUP查找返回多个数据，显然我们需要写入或复制多个公式。或者用MATCH函数充当第三参数，然后复制这个嵌套公式。写的时候还要注意引用方式（相对引用/绝对引用/混合引用）。
在2019版本中，新增了动态数组函数。同时在XLOOKUP其实也可以应用到动态数组的概念了。
也就是说，如果结果返回的是一组数据，那么返回的结果会“自动溢出”到旁边的单元格。“自动溢出”在Excel里面是一个全新的概念。
这样做的好处非常明显：
- 只需要写入一个函数即可返回多个单元格的结果；
- 由于返回的数组用了自动溢出的概念，所以写公式的时候不需要考虑单元格引用方式（用相对引用就可以了），公式会显得更简洁。
需要注意的是，被溢出的单元格区域需要保持空值状态，否则会出现#SPILL!错误。
案例3 模糊匹配
查找数据，免不了会用到模糊匹配。尤其是在查找判断数据区间的时候。
LOOKUP函数在使用的时候默认模糊匹配。但是，用LOOKUP函数进行查找的时候，查找逻辑是用“二分法”，所以要求被查找的数据列必须【升序排列】。如果找不到精确匹配的数据，那么就会返下一个比较小的项（注意了，这是默认规则，不能返回下一个比较大的项哦）。
但是XLOOKUP利用第5参数，可以很简单地返回精确匹配数据或者下一个较小或者下一个较大的项，同时也支持“通配符”。
但是最最最重要的是，用XLOOKUP进行模糊匹配，并不要求被查找数据列进行升序排列！要知道在LOOKUP函数中，这是一个隐藏得很深很容易发生错误的地方。一旦没有升序排列，LOOKUP不会出现错误值，而是依然会返回一个“不可预知”的数据，但是很可能返回的数据是错误的。
单单就这个特性来说，XLOOKUP就已经足够强大了。
案例4 XLOOKUP嵌套使用取代INDEX+MATCH
在以往版本的查找函数中，有一个经典组合INDEX+MATCH，MATCH函数用于返回行号或者列号，INDEX函数根据返回的行号和列号定位到要返回的数据。
现在，同样的功能，也可以用XLOOKUP函数来实现。通过嵌套XLOOKUP函数，返回指定行指定列的数据。
这个案例中，实现同样的结果，还是XLOOKUP的公式写法更简便。而且，理解XLOOKUP可以返回一组数据在内存中供调用，对于以后再其它函数中嵌套使用也是很有帮助的。
一些嵌套的新用法，有待我们继续挖掘。
案例5 区间求和/区间计数
再深入一点研究，就会发现XLOOKUP不仅仅能返回单元格中的数据，而且在特定情况下还能返回单元格的名称，比如返回“A1，B2”这样的名称。
这样的话就给了我们更多的想象空间，比如这个案例的区间求和/区间计数。用SUM函数或者COUNT函数和XLOOKUP嵌套使用。
如果不使用XLOOKUP实现区间求和，我们可以利用以往函数进行一些技巧性的组合使用当然也能实现，但是显然公式要复杂得多。
总结
通过上面的几个案例演示，以及和旧版本函数的对比，相信你已经对XLOOKUP有相当多的了解了。
不得不承认，XLOOKUP确实太强大了！在上面的案例我们可以看出XLOOKUP可以取代旧版本的一些函数，或者大大简化函数公式的写法。
XLOOKUP肯定还有其他方面的用法，需要我们继续挖掘TA的潜能。
扫一扫
4122
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
