37岁的VLOOKUP要光荣退休了？这个新函数好用到爆哭！！

点击蓝字【秋叶 Excel】👆

发送【交流】

立即进秋叶 Excel 读者快乐交流群！

本文作者：拉登 Dony、竺兰

本文编辑：小音

Vlookup 可以说是 Excel 使用最广泛的函数之一。

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

早在 2019 年，微软官方就放了个大招，发布 Xlookup 函数，但是只有 Office 365 版本才可以使用；去年 10 月 WPS 官方也突然公布：现在下载最新 WPS Windows 版，就可以免费使用 Xlookup 函数。

和 Vlookup 相比，只是字母从 V 变成了 X，功能却强大了好几倍！

接下来，我们先了解一下「什么是 Xlookup？」，再通过 6 个常见的需求，看看 Xlookup 函数到底厉害在哪儿~

PS.本文演示版本：WPS Office 最新版；练习文件获取方式见文末。

普通查找

Vlookup 我们都再熟悉不过啦，主要就是查询匹配数据。

Xlookup 的用法也是一样滴，But 它还可以看做是 Lookup 家族的合体版！！

Lookup 家族：Vlookup、Hlookup、Lookup、Index、Match。（可见合体版有多强大……）

举个栗子！

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

用 Xlookup 来实现，简单的很~

在 J4 单元格中输入公式：

=XLOOKUP(I4,C4:C8,E4:E8)

公式是啥意思呢？解释一下：

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

对应上图一起来看，是不是很好理解。

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

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

But 用 Vlookup，我们需要写 4 个参数……

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

公式如下：

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

解释一下就是：

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

这样一比较，你是不是发现 Xlookup 函数在普通查找中表现更优秀？

多条件查找

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

切，不就是少了一个参数，值得把 Xlookup 吹上天吗？你个喜新厌旧的坏人！

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

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

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

先用 Xlookup 函数，把公式写出来给你们看：

什么意思呢？思路是这样的：

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

=XLOOKUP(I5&J5

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

=XLOOKUP(I5&J5,B4:B12&C4:C12

❸ 最后，选择「返回的区域」为：

=XLOOKUP(I5&J5,B4:B12&C4:C12,E4:E12)

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

但同样的思路，用 Vlookup 就复杂了……

光说这公式，我就先晕辽：

=VLOOKUP(I5&J5,IF({1,0},B4:B12&C4:C12,E4:E12),2,0)

▲ 左右滑动查看

参数 1，用&符号，把部门和姓名合并在了一起。

参数 2，用 IF 函数，构建查找列 B4:B12&C4:C12 和返回列 E4:E12。

参数 3，返回第 2 列。

参数 4，精确查找。

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

IF({1,0},B4:B12&C4:C12,E4:E12)

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

看不明白吧？看不明白就对了！

这不就说明了，Xlookup 更好用嘛！

未找到值

当我们在查找一个不存在的值，表格通常会返回一个错误乱码。

这时，在 Xlookup 中，可以添加一个较为特殊的参数[未找到值]，它的作用是在我们查找不存在的值时，能够返回指定内容。

来看看这个案例，查询「丽丽」所在的「入职日期」。

用 Xlookup 函数查询很简单，公式如下：

=XLOOKUP(I5,C4:C12,F4:F12)

但是结果……是这样滴……

这说明公司里并没有这名员工，所以入职日期这一栏才会显示#N/A。

如果我们在[未找到值]填入一个信息，比如「查无此人」（需要用英文双引号框起来），那么单元格就会显示设定好的内容：

而 Vlookup，不用说，它并没有这样的功能。

反向查找

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

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

非常简单嘛，和按「姓名」查找「在职状态」没两样啊？！

fine，咱先不说 Xlookup，没有对比就没有伤害，先看 Vlookup。

用过 Vlookup 同学都知道，它有一个通病：只能从左往右查找。

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

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

公式如下：

=VLOOKUP(I5,IF({1,0},C4:C12,B4:B12),2,0)

▲ 左右滑动查看

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

再看 Xlookup，一如既往地简单优雅：

=XLOOKUP(I5,C4:C12,B4:B12)

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

再说了，这里查找「小叶」的部门时，因为有两个小叶（重名）。

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

如果我想查找在「客服部」的「小叶」，要怎么写呢？

给 Xlookup 加个参数「0,-1」就可以了：

=XLOOKUP(I5,C4:C12,B4:B12,,0,-1)

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

0 表示匹配模式为精确匹配。

-1 表示从下往上查找；如果输入 1，表示从上往下查找；输入 2，表示升序排序的二进制文件搜索；输入-2：表示降序排序的二进制文件搜索。（后两种搜索模式一般不用）

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

一对多查找

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

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

=VLOOKUP($I5,$C$4:$G$12,COLUMN(B1),0)

▲ 左右滑动查看

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

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

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

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

=XLOOKUP(I5,C4:C12,D4:G12)

注意！返回数组须框选所有区域，输入公式后按下【Ctrl+Shift+Enter】  才能得出正确结果。

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

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

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

高效又简单，大家快给我夸夸！

模糊查找

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

评比规则如下：

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

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

▲ 左右滑动查看

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

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

在 I4 列输入公式：

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

糟糕，好像翻车了……

小伙伴们注意啦！！！

在使用这种模糊查找方法时，「绩效」列的数字必须从小到大排序，否则查询就会出错。

正确操作：

但是！没错，但是来了——

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

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

▲ 左右滑动查看

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

参数 4 有下面几种用法：

0 表示精确匹配，若未找到所查找内容返回#N/A。

2 表示可使用通配符匹配 。

1 表示精确匹配，若未找到所查找内容返回较大项。

-1 表示精确匹配，若未找到所查找内容返回较小项。

 

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

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

写在最后

我们再来总结一下：

怎么样，看完这篇文章，是不是感觉 Xlookup 比 Vlookup 强大了好几倍呢？

如果你有不同的见解，也欢迎积极留言交流哦~

另外，Xlookup 函数的所有参数含义，我贴在下面了，伙伴们可以直接点击图片保存：

如果你想要本文的练习文件，在我们公众号后台回复：查找 即可（点下方的卡片可以进入后台，把菜单栏收起来，就可以给公众号打字发消息啦~）

如果这篇文章对你有帮助，请帮忙「点赞」「在看」「转发」。

这对我很重要，能给我更多动力，持续分享优质的内容。

秋叶家春季福利来袭——《3 天 Excel 集训体验营》

专为职场职场人准备，全部基于职场真实表格案例设计，超实用 Excel 技巧集合教学。

从此不愿再为表格而加班，那就行动起来！3 天搞定表格难题！

《秋叶 Excel 3 天集训营》

想提升，趁现在！

福利名额 仅限前 50

不再被加班支配，不再为表格发愁！

免费赠送【35 个常用函数说明】哦！

赶紧扫码预约吧！👇👇👇

▲ 报名成功后将自动弹出班主任添加二维码，切勿提前退出

若未能添加，请联系公众号客服获取

欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

遇到有价值的文章

不放过 !

👇👇👇

动动小手

分享给朋友~

👇👇👇