【原】XLOOKUP+LAMBDA优雅解决空值返回为0的问题

2000元课程

函数+VBA

数字0和空，虽然都能用于表示“没有”，但由于Excel严格谨慎的数据类型划分，两者不能视为相等。

但在用XLOOKUP进行查找时，本来应该返回空值的，却会返回0，这会严重影响后续的数据解读或运算。

例如，下图所示的案例中哈密瓜返回了0：

=XLOOKUP(D2,A:A,B:B)

这一现象并非针对XLOOKUP,所有引用类函数都一样，包括VLOOKUP,HLOOKUP,INDEX,OFFSET等。

以下介绍3种解决方案，各有长短，适合于不同的场景。

设置格式

保持公式不变，通过【设置单元格格式】将结果列的格式设置为：0;-0;

其原理是将0显示为空，所以会有一个缺点：如果原数据中有0，也会被显示为空。

连接空值

将数字和空值连接，其格式有“数值”转换为“文本”，0转换为文本后会变成空值。

所以在XLOOKUP的最后连接一个空值：

=XLOOKUP(D2,A:A,B:B)&""

这个方法的缺点是：所有数字都会被转换为文本格式，文本型数字作为函数参数时不能被作为正常数字对待的，比如我们对上一步的结果用SUM求和：

=SUM(E2:E3)

所以XLOOKUP的结果需要参与更多公式运算的场景中这个方案又会带来一些麻烦，但并非不能解决。通过四则运算可以把文本型数字又转换为正常数字，例如：

=SUM(IFERROR(--E2:E3,0))

IF判断

用IF函数XLOOKUP的返回值是否不等于空，判断成立则输出XLOOKUP的结果，否则输出空：

=IF(XLOOKUP(D2,A:A,B:B)<>"",XLOOKUP(D2,A:A,B:B),"")

这个方案规避了前2个方案中的缺点，空返回空，0返回0，返回的数字也是正常数字。

以上3个方案同样适用于一开始提到的函数。

最终解决方案 lamda定义if函数

if方法里显示要写两次vlookup很繁琐，而且可能很多地方都要这么用，这就可以使用lambda函数了，在自定义名称中定义lamda函数 ifEmpty

然后其他这种有查找的位置就可以使用ifEmpty 非常简洁

ifEmpty(XLOOKUP(D2,A:A,B:B))