电脑装配网

34岁的VLOOKUP要光荣退休了?这个新函数好用到爆

 人阅读 | 作者yiyi | 时间:2023-07-11 01:45

VLOOKUP 函数是超好用的函数(谁用谁知道),职场高效办公离不开它~

不知不觉,它已经陪我们度过了 34 个春秋,它让千千万万人免于加班脱发之苦,然鹅……

上周微软官方放了个大招,发布最新函数:XLOOKUP。

和 VLOOKUP 相比,只是字母从 V 变成了 X,功能却强大了好几倍!

接下来,我们通过 5 个常见的需求,看看 XLOOKUP 函数到底厉害在哪儿~

01 普通查找

VLOOKUP 主要用途就是查询匹配数据。

XLOOKUP 的用法也是一样滴,but 它还可以看做是 LOOKUP 家族的合体版!!

LOOKUP 家族:VLOOKUP、HLOOKUP、LOOKUP、INDEX、MATCH。(可见合体版有多强大……)

举个栗子!

这里我们要根据「姓名」,查询每个人的「在职状态」。

用 XLOOKUP 来实现,简单的很~

在 J4 单元格中输入公式:

=XLOOKUP(I4,C4:C19,E4:E19)

公式是啥意思呢?解释一下:

=XLOOKUP(要查找的值,查找的区域,返回的区域)

对应上图一起来看,是不是好了解一点?

XLOOKUP 函数的优势就在于,在确定要查找的区域、返回的区域时,我们可以直接选中一整列,比如这里就选中了 C 列、E 列数据~

这样公式只需 3 个参数就搞定啦!

but 用 VLOOKUP,我们需要写 4 个参数……

而且!要一次性选中整个区域,再来挨个数数,看返回的区域在整个区域中排第几位……

公式如下:

=VLOOKUP(I4,C3:E19,3,0)

解释一下就是:

=VLOOKUP(要查找的数值,查找的区域,查找返回列,查找模式)

这样一比较,你是不是发现 XLOOKUP 函数在普通查找中表现更友好一些?

02 多条件查找

写到这里隔壁小 E 跑过来吐槽:

切,不就是少了一个参数,值得把 XLOOKUP 吹上天吗?你个喜新厌旧的坏人!

那我们再来看一个案例,还是根据「姓名」,查询「在职状态」。

但是……眼神好的同学一定发现啦,数据中有两个同名的「秋叶」,直接查找一定会出错!

所以,要同时根据「部门」和「姓名」,来查找在职状态~

先用 XLOOKUP 函数,把公式写出来给你们看:

什么意思呢?思路是这样的:

❶ 把查询结果中,「部门 I 列」和「姓名 J 列」合并,一起作为「要查找的值」:

=XLOOKUP(I4&J4,B4:B19&C4:C19,E4:E19)

❷ 选择「查找区域」时,也把「部门 B 列」和「姓名 C 列」合并起来查找:

=XLOOKUP(I4&J4,B4:B19&C4:C19,E4:E19)

❸ 最后,选择「返回的区域」为:

=XLOOKUP(I4&J4,B4:B19&C4:C19,E4:E19)

我们会发现,XLOOKUP 函数居然可以直接用&符号,把列合并起来!这也太方便了吧!

但同样的思路,用 VLOOKUP 就复杂了……

光说这公式,我就先晕辽:

=VLOOKUP(I4&J4,IF({1,0},B4:B19&C4:C19,E4:E19),2,0)

参数 1,用&符号,把部门和姓名合并在了一起。参数 2,用 IF 函数,构建查找列 B4:B19&C4:C19 和返回列 E4:E19。参数 3,返回第 2 列。参数 4,精确查找。

参数 2 的写法,实在是看不懂啊。这里还构建了一个动态区域:

IF( {1,0} , B4:B19&C4:C19 , E4:E19)

这段公式相当于构建了下面的数据:

看不明白吧?看不明白就对了!

这不就说明了,XLOOKUP 更好用嘛!

03 反向查找

前面一个案例,相信大家已经感受到 XLOOKUP 的简单、好用了,但是离上天,还差点儿。

再来看这个需求,要根据「姓名」查询「部门」。

非常简单嘛,和按「姓名」查找「在职状态」没两样啊?!

fine,咱先不说 XLOOKUP,没有对比就没有伤害,先看 VLOOKUP。

用过 VLOOKUP 同学都知道,它有一个通病:只能从左往右查找。

也就是说这里需要「姓名」在左边,「部门」在右边,才方便查找。

而表格中「部门」在左边,所以查找起来会很麻烦。

公式如下:

=VLOOKUP(I4,IF({1,0},C4:C19,B4:B19),2,0)

我天,又是 IF({1,0})的方法,再见。

再看 XLOOKUP,一如既往地简单优雅:

=XLOOKUP(I4,C4:C19,B4:B19)

XLOOKUP 在选择时,只需要分别选择查找列和返回列就行,所以根本不存在左右的问题~

再说了,这里查找「秋叶」的部门时,因为有两个秋叶(重名)。

而 VLOOKUP 默认只能查找到第 1 条记录,也就是「生产部」。

如果我想查找在「客服部」的「秋叶」,要怎么写呢?

给 XLOOKUP 加个参数「0,1」就可以了:

=XLOOKUP(I5,C5:C20,B5:B20,0,-1)

「0,1」这个参数并不难,来解释一下:

0 表示匹配模式为精确匹配。-1 表示从下往上查找;如果输入 1,表示从上往下查找。

所以填写 -1,就能找到最下面位于「客服部」的「秋叶」~

04 一对多查找

现在我们要根据姓名,把员工的全部信息都查找出来,共计 4 列,所以返回值也有 4 个。

如果用 VLOOKUP 函数,为了解决返回列变化的问题,需要结合 COLUMN 函数来写公式。

=VLOOKUP(I5,C4:G19,COLUMN(B1),0)

公式填写好之后,向右拖动填充即可。

但……如果你不会 COLUMN 函数,解决这个问题最好的方法,就是赶紧关掉 Excel,眼不见为净。

在这个问题上,XLOOKUP 的处理方式会更高级。

简简单单一个公式就搞定:

=XLOOKUP(I4,C3:C19,D3:G19)

奥秘就在第 3 个参数「D3:G19」上。

参数 3,选择返回列的时候,把所有需要返回的列,一次性都选上。

聪明的 XLOOKUP 同学,会根据返回列的列数,自动填充相邻的数据列~

高效又简单,大家快给我夸夸!

05 模糊查找

这里我们要根据 G 列的「绩效」,算出 I 列的「绩效评比」结果。

评比规则如下:

我猜,很多人遇到这种问题,都会写长长的 IF 函数吧?

=IF(N4>=100,"A+",IF(N4>=90,"A",IF(N4>=80,"B",IF(N4>=70,"C",IF(N4>=60,"D","E")))))

这种情况其实可以用 VLOOKUP 解决~

在一些绩效、提成的计算上,用 VLOOKUP 模糊查找,可以避免反复地写 IF 函数嵌套。

在 I4 列输入公式:

=VLOOKUP(G4,$L$4:$M$9,2,1)

要注意的是,这种模糊查找方法,要求「绩效」列的数字从小到大排序,否则查询会出错。

但是!没错,但是来了——

XLOOKUP 的解决方法,更加简单易懂,改一下参数就好。

=XLOOKUP(G4,$L$4:$L$9,M4:M9,-1)

前 3 个参数和 VLOOKUP 道理一样,重点是第 4 个参数:设置查询匹配的模式。

参数 4 有下面几种用法:

0 表示精确匹配。1 表示精切匹配,或下一个较大的项。如果找不到,就找比查找值大的最接近的值。-1 表示精切匹配,或下一个较小的项。如果找不到,就找比查找值小的最接近的值。

这样一来,不管「绩效」列的数字按什么顺序排,都不会影响我们查找结果!

又比 VLOOKUP 函数节省了好几分钟呢~

温馨提示,XLOOKUP 目前只有 Office 365 版本的 Excel 才可以使用。

可能有的同学会说了,我连 VLOOKUP 还没用顺溜了,怎么又来了个新的?

没关系,这里有《零基础Excel快速入门》课程,一个月让你从Excel小白变大神!

点击上面的专栏卡片,课程可以免费试看哟~


文章标签:

本文链接:『转载请注明出处』