新函数XLOOKUP详解，VLOOKUP自愧不如、自惭形秽、自求下岗

最近推送的五篇文章：

·  正  ·  文  ·  来  ·  啦  ·

前言

八月底，微软在开发体验版中推出了XLOOKUP，之前已经写了一篇文章介绍VLOOKUP函数的局限性，

惊呆！微软要抛弃VLOOKUP函数了

由于龙逸凡当时没有更新到最新版，没这个函数，只是利用该函数帮助中的示例图片简单地介绍了一下其功能。

现在详细介绍一下其用法，让大家体验一下它的强大。

XLOOKUP函数基础知识
语法格式：
用法示例：
注意：
上图是在F2:H3单元格利用XLOOKUP函数在B列查找5，要返回的C列对应的值。当XLOOKUP第四、第五参数分别为0、1和-1时返回的结果是不同的，为了方便大家理解，已将B列的值剔除重复值，升序排列放在E7:E14单元格区域。
G2单元格公式
=XLOOKUP(5,$B$3:$B$14,$C$3:$C$14,-1,1)
在B列从上往下查找5，没找到，就返回比它小的最大值（B9单元格的4.5）C列所对应值“甲A7”。
G3单元格公式：
=XLOOKUP(5,$B$3:$B$14,$C$3:$C$14,-1,-1)
在B列从下往上查找5，没找到，就返回比它小的最大值（B13单元格的4.5）C列所对应值“甲A11”。
H3单元格公式：
=XLOOKUP(5,$B$3:$B$14,$C$3:$C$14,1,1)
在A列从上往下查找5，没找到，就返回比它等大的最小值（B8单元格的6.3）C列所对应值“甲A6”。
H4单元格公式：
=XLOOKUP(5,$B$3:$B$14,$C$3:$C$14,1,-1)
在A列从下往下查找5，没找到，就返回比它等大的最小值（B10单元格的6.3）C列所对应值“甲A8”。
典型应用案例1：返回指定日期的余额
这是财务工作中的实际应用案例。
查找5月31日的余额，明细账中没有5月31日，就返回5月11日最后一笔的余额：
=XLOOKUP(E1,$A$5:$A$34,$F$5:$F$34,-1,-1)
从下往上找，如果没找到，就返回比它小的最大值对应的数据
典型应用案例2：查找适用的个人所得税税率
=XLOOKUP($B5,$D$14:$D$19,E$14:E$19,1,1)
扩展应用
1、逆向查找（从右往左查找）：
=XLOOKUP(A14,$B$4:$B$10,$A$4:$A$10)
2、替代HLOOKUP横向查找
=XLOOKUP(A12,B2:F2,B4:F4)

3、多条件查找

=XLOOKUP($E2&F$1,$A$2:$A$21&$B$2:$B$21,$C$2:$C$21,0,1)

4、还可打批发，一次查找多个
=XLOOKUP($A$11:$A$13,$A$3:$A$8,$C$3:$C$8)
对查找出的多个结果进行求和：
=SUM(XLOOKUP($A$11:$A$13,$A$3:$A$8,$C$3:$C$8))
查找多个单元格时，还可以是横向或矩形区域：
=XLOOKUP($A$21:$B$22,$A$3:$A$8,$C$3:$C$8)
5、纵向横向交叉查询
交叉查询没有INDEX+MATCH就不能干活了？
XLOOKUP一个人就搞定了！
=XLOOKUP(A11,A3:A8,B3:F8,0,1) XLOOKUP(A12,B2:F2,B3:F8,0,1)
上面的公式，两个XLOOKUP之间是一个空格
也可以用下面这个公式实现交叉查询：
=XLOOKUP(A11,A3:A8,XLOOKUP(A12,B2:F2,B3:F8,0,1))
6、一个公式就返回多列
=XLOOKUP(A12,$A$3:$A$8,$B$3:$F$8,0,1)
注意，上面公式XLOOKUP的第3参数是一个矩形区域。利用了动态数组的自动溢出功能。
7、返回第一个XX值
返回第一个非空单元格的值：
=XLOOKUP(1,--NOT(ISBLANK(A2:A17)),B2:B17)
返回第一个文本值：
=XLOOKUP(1,--ISTEXT(A2:A17),B2:B17)
返回第一个数字：
=XLOOKUP(1,--ISNUMBER(A2:A17),A2:A17)
返回第一个非零值：
=XLOOKUP(1,--(A2:A17<>0),B2:B17)
8、返回最后一个XX值
返回最后一个非空单元格值：
=XLOOKUP(1,--NOT(ISBLANK(A2:A17)),B2:B17,0,-1)
其余类推
9、查找最新单价
=XLOOKUP(MAX((B2:B13=E1)*A2:A13),(B2:B13=E1)*A2:A13,C2:C13,0,1)
10、提取唯一值列表
数据表如下：
按出现的顺序提取唯一值列表
=IFNA(XLOOKUP(0,0/(COUNTIF(B$4:B4,数据表!$A$2:$A$54)=0),数据表!$A$2:$A$54),"")
如果用的是新版Excel，其实不用这么复杂，一个UNIQUE函数就搞定了
=UNIQUE(销售明细[客户名称])
11、指定条件的唯一值列表
提取“润发国际集团”所销售的产品的列表：
=IFNA(XLOOKUP(0,0/((数据表!$A$2:$A$54=$C$3)*(COUNTIF(B$4:B4,数据表!$C$2:$C$54)=0)),数据表!$C$2:$C$54),"")
新版Excel，联用FILTER和UNIQUE函数更简单：
=UNIQUE(FILTER(销售明细[商品名称],销售明细[客户名称]=C3,"商品名称"))

新版的Excel有哪些好用的新功能和新函数，请参考下面的链接：

Excel 2019 新增了哪些实用功能？（附正式版下载地址）
OFFICE 365的这些功能，颠覆了我对Excel的认知①
OFFICE 365的FILTER函数，颠覆了我对Excel的认知②
OFFICE 365的两个筛选函数，颠覆了我对Excel的认知③
亲，现在都是9102年了，就别再用Excel 2003、Excel 2007、Excel 2010了，不装19，好歹装一个13呗
本文知识点

  • XLOOKUP函数

  • UNIQUE函数

  • FILTER函数

毒 鸡 汤

别说你一无所长，
熬夜看抖音、刷微博
你可是一把好手

Excel畅销书推荐：

《“偷懒”的技术2：财务Excel表格轻松做》

《偷懒2》不是《偷懒1》的改版，两者内容是完全不同的，《偷懒2》主要介绍灵活如何用函数公式、功能技巧专业地设计表格。并将日常工作常用的公式设计成模型公式，要用的时候，直接比照套用就是了。
宗旨是：
表格不会做，照搬即可；
公式不会写，套用就行。

《“偷懒”的技术：打造财务Excel达人》

2017年当当网畅销榜Excel类第一名，办公类第二名，好评率99.8%，学Excel必选书籍！

👇滑动下面的列表查看更多

如何正确使用本公众号，学习Excel技巧，提高工作效率

【目录】本公众号2017年推送文章的分类导航

【目录】本公众号2018年推送文章的分类导航

怎样才算精能Excel？看完再也不敢在简历上写精通Excel了！

怎样才能让Excel运行得更快、从此告别卡慢等

强大到逆天的“快速填充”，不用公式提取字符、调换位置

你真的理解了相对引用？95%的人都错了，你呢？

按年、季、月、旬、周分段求和，这一篇文章总结完了

根据指定的条件，统计唯一值的个数，公式总结

行列交叉查询公式汇总及解释

如何按简称查找全称、如何反向模糊查找

财务工作经典Excel公式及解析

使用vlookup函数的常见错误及解决方法

深入讲解SUMIF&多表多列多条件求和

用sumif对超15位的代码条件求和居然出错了，原因是...

一张图表示实际VS半年及年度预算完成情况
要做出别具一格的图表都要用到这个强大的功能...

普通的折线图蜕化成蝶后，美到你认不出来

手把手教你制作华丽酷炫的走势图

不用辅助列也可制作旋风图、蝴蝶图

财务分析如何做到一图胜千言

财务分析经典图表及制作方法(第1季)

财务分析经典图表及制作方法(第2季)

豪华仪表盘模板下载

制作高大小的圆环图，这个方法更简单

超越图表大神的小技巧：在柱形图背后添加平均线

不等宽的堆积柱形图，这思路开脑洞...

如何用箭头标注指标的同比增减情况？

赞 (0)

相关推荐