XLOOKUP这个神仙函数好用到爆哭

普通查找

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

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

在 J4 单元格中输入公式：

• 
  

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

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

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

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

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

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

多条件查找

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

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

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

先用 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 的简单、好用了，但是离上天，还差点儿。

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

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

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

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

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

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

公式如下：

• 
  

=VLOOKUP(I4,IF({1,0},C4:C19,B4:B19),2,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，就能找到最下面位于「客服部」的「秋叶」~

一对多查找

现在我们要根据姓名，把员工的全部信息都查找出来，共计 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 同学，会根据返回列的列数，自动填充相邻的数据列~

模糊查找

这里我们要根据 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 和最新版本的 WPS 可以使用。