最近,被学员提起最多的就是微软在2019年8月底公布的XLOOKUP函数的测试版,一看名字就猜到了,这货一定是VLOOKUP函数的增强版。
之前我的Office365订阅版本还不能够使用XLOOKUP,前几天又被学员问起了,我打开Excel尝试着输入“=XLOOKUP”发现这货不知道什么时候已经出现在我的Excel中了,正好,赶紧用起来,看看着传说中要取代VLOOKUP的XLOOKUP到底怎么用?
其实,XLOOKUP能做到的,之前的版本用LOOKUP,VLOOKUP、HLOOKUP或者MATCH+INDEX也能做到,所以,有些Excel大咖诟病微软没有创新,实际上,卓哥认为:真正有意义的更新,一定是让原本复杂的工作变得简单,XLOOKUP实实在在的就做到了。世界各地的办公族们欢欣鼓舞。
微软刚刚发布的XLOOKUP函数时,只有5个参数,现在增加了“如查询不到,返回的内容”这一个参数,增加这一项,相当于省略了ifna或iferror函数。现在的XLOOKUP函数一共有6个参数:
=XLOOKUP(查找值,查找值所在的列,结果所在的列,如查询不到返回的值,[匹配方式],[搜索模式])
1、 查找值,指定的单元格也可以是数组(区域)
2、 值所在的列或者区域
3、 结果所在列或者区域
4、 如查询不到,返回的内容
5、 多种匹配方式
0:精确匹配
-1:如果未找到查询值, 则返回比查询值小的下一个项目。向上查询比查找值小但跟查找值最近的哪一项内容的结果。同vlookup的近似匹配。
1:如果未找到查询值, 则返回比查询值大的下一个项目。向下查询比查找值大但跟查找值最近的哪一项内容的结果。
2:通配符的匹配。*号为字符串(多字符),?为单字符。
6、 搜索模式
1:表示从第一项开始执行搜索
-1:表示从最后一项开始执行反向搜索
2:表示在查找区域为升序的前提下搜索
-2:表示在查询区域为降序的前提下搜索。如果未排序, 将返回无效的结果。
XLOOKUP函数真正在使用过后,才发现XLOOKUP就是Excel中“查找圈儿”的“大BOSS”,VLOOKUP、HLOOKUP、LOOKUP,MATCH、INDEX这些过去能独挡一面的函数,将来可能只需要XLOOKUP这一个就够了。
常规的就不分享了,分享几个XLOOKUP在使用过后
01反向查询要通过“产品”查找“销售人”,“销售人”在“产品”的左边。借助XLOOKUP,你可以在一列中查找搜索词,并在同一行的另一列中返回结果,无论返回结果的列在原列的哪一侧。
其实,反向查询LOOKUP函数也能够胜任,或者VLOOKUP嵌套IF也OK,大不了就把两列数据换个位置也OK,所以,XLOOKUP只是方便一些。
02一次性查找多列的数据XLOOKUP 可以返回具有多个项的数组,这允许单个公式从单元格B2:C11一次性的返回地区和销售人。重点是函数写在一个单元格中,返回的确是多项内容,这波操作有点类似数组函数,但又完全是单个函数的做法。有点意思!
如下图所示,在C14单元格内设置公式查找内容为这B14:B16连续3个单元格,这样就可以同时返回3个查找值对应的结果。XLOOKUP查找时只需要在第一个单元格中设置公式。
如图在做区间查找时,匹配方式为(-1)时,则返回小于查找条件的最大值对应的结果,简单说,就是去找比查询值要小,但是跟查询值最近的哪一项对应的值。
比4560小,但是跟4560最接近的就是3000,所以,对应的结果是D。
05向下查询的近似匹配如图在做区间查找时,匹配方式为(1)时,则返回大于查找条件的最小值对应的结果,简单说,就是去找比查询值要大,但跟查询值最近的哪一项对应的值。
比4560大,但跟4560最接近的就是6000了,对应的值是E
06 通配符*号对包含某个字符串的内容进行查找,如果有多个内容则找出第一次出现的。
?表示单个字符,??水,表示把"水"前面有两个字的内容查找出来。
"汽"水是一个字符,"矿泉"水是两个字符,所以,“??水”就是“矿泉水”对应的结果找出来了。
XLOOKUP函数可以对查找内容使用&连接符进行多条件的查找。
从第一项到最后一项进行搜索
(至上而下)
XLOOKUP函数的最后一个参数如果设定为1,则表示从第一项到最后一项进行搜索,即便是查找内容出现重复,也只是取第一次出现时对应的内容。
从最后一项到第一项进行搜索
(至下而上)
XLOOKUP函数的最后一个参数设定为-1,则表示从最后一项到第一项进行搜索,把由后往前第一次出现查找值所对应的查找结果提取出来。
按照行号进行上下的查找,跟HLOOKUP类似,用法跟前面反向的查询和普通正向查询一样。
值得提醒大家的是XLOOKUP函数目前只有部分的Office365用户能够使用到,可见微软还是更重视它的Office365订阅,如果不是Office365订阅用户可能也无法使用该用功能,所以,这个函数要等到普及估计还有一段时间。
XLOOKUP是一个不折不扣的20后,85年的VLOOKUP已经35岁”高龄“了,是时候退休了,在这里卓哥也要特别感谢伴随了4代人的VLOOKUP,因为有了它,我们的工作才更高效,谢谢,再见!