比Vlookup更逆天！这个强大的查询函数，看完我就跪了！

原创赵骄阳秋叶Excel

点击蓝字【秋叶 Excel】👆

发送【交流】

立即进【秋叶同学会】交流 Excel！

本文作者：赵骄阳

本文编辑：竺兰

Hi，我是偏爱函数公式，爱用 Excel 图表管理仓库的大叔 Mr 赵~

前段时间秋叶 Excel 发了篇讲 LOOKUP 的文章（链接见文末），有同学留言问为什么不讲 XLOOKUP 函数？

我觉得可以！

所以我们今天就来讲讲 XLOOKUP，一个强大的查询函数！

在说 XLOOKUP 函数之前，咱们先看看下面这个案例，根据姓名查找对应的性别。

这个问题太常见了！首先想到的就是用 VLOOKUP 函数。

=VLOOKUP(E2,A2:C7,2,0)

在查找区域 A2:C7 的首列找到 E2 单元格的值「王五」，返回查找区域 A2:C7 第 2 列与之对应的值「男」。

初识 XLOOKUP 函数

再来看看 XLOOKUP 函数的用法：

=XLOOKUP（查找的值，查找范围，结果范围）

公式就可以这样写：

=XLOOKUP(E2,A2:A7,B2:B7)

在查找范围 A2:A7 中找到 E2 单元格的值「王五」，返回 B2:B7 对应的值「男」。

如果姓名这列不是在前面，用 VLOOKUP 函数似乎就不太合适了。因为 VLOOKUP 函数的规则是在查找区域的首列查找。

这种情况下，我们通常会用 INDEX 函数和 MATCH 函数组合写公式：

=INDEX(A2:A7,MATCH(E2,B2:B7,0))

MATCH 函数找出 E2 的值「王五」在 B2:B7 中是第几行，得到结果 3，然后用 INDEX 函数将 A2:A7 的第 3 行的值引用出来，得到结果「男」。

而 XLOOKUP 函数就不一样了，它不会像 VLOOKUP 那样受位置的影响，依然照用不误：

=XLOOKUP(E2,B2:B7,A2:A7)

在查找区域 B2:B7 中找到 E2 单元格的值「王五」，返回 A2:A7 对应的值「男」。

XLOOKUP 函数的第 4 参数

经常有小伙伴提这样的问题，如何让 VLOOKUP 查找不到的数据返回为空值？

如下图，VLOOKUP 函数在查找区域 A2:B7 的首列没有找到单元格的值「孙二」,就会返回错误值#N/A。

=VLOOKUP(D2,A2:B7,2,0)

通常我们都会在 VLOOKUP 函数外层嵌套 IFERROR 函数，或者用 IFNA 函数来容错。

=IFNA(VLOOKUP(D2,A2:B7,2,0),"")

而 XLOOKUP 函数有它专属的参数：它的第 4 个参数专门负责容错。

=XLOOKUP（查找值，查找范围，返回范围，[容错]）

这个参数是非必需参数，当你碰到上面这种问题，才有必要把它请出来。

公式就可以写成：

=XLOOKUP(D2,A2:A7,B2:B7,"")

在查找范围 A2:A7 中找 E2 单元格的值「孙二」，如果有找到就返回 B2:B7 对应的值，如果没有找到，则返回第 4 参数指定的内容「""」。

当然第 4 参数的设定并非只可以是字符串，数值。也可以嵌套其它的公式返回结果。

XLOOKUP 函数的第 5 参数

早前有一篇根据得分评定等级的文章：

年终 KPI 评定来了！4 种公式套路帮你搞定！

用 XLOOKUP 函数同样可以轻松解决。

以下是评定的规则：

小于 60 分不合格；

大于等于 60 小于 70 为合格；

大于等于 70 小于 80 为良好；

大于等于 80 为优秀。

先为每个等级设置分数的下限，如下图 A 列，然后在 E2 单元格写入公式：

=XLOOKUP(D2,A2:A5,B2:B5,,-1)

XLOOKUP 函数的第 5 参数是匹配类型。

=XLOOKUP（查找值，查找范围，返回范围，[容错]， [匹配类型])

当第 5 参数的值为-1 时，表示如果「查找值」没有在「查找范围」中，就返回下一个较小的值。

如上面的公式中，D2 单元格的值是 75，没有在查找区域 A2:A7 中，就找比 75 小的值，即 70。再返回 B2:B5 对应的等级「良好」。

如果把 A 列的分数下限改成上限，公式就可以这样写：

=XLOOKUP(D2,A2:A5,B2:B5,,1)

当第 5 参数为 1 时，表示如果「查找值」没有在「查找范围」中，就返回下一个较大的值。

如公式中，D2 单元格的值是 75，没有在查找区域 A2:A7 中，就找比 75 大的值，即 79。再返回 B2:B5 对应的等级「良好」。

写到最后

关于 VLOOKUP、XLOOKUP 和 LOOKUP 有哪些区别，以下 Tips 供大家参考：

❶ VLOOKUP 函数必须在查找区域的首列查找，而 XLOOKUP 函数不受这种位置限制；

❷ VLOOKUP 函数需要其它函数嵌套来容错，而 XLOOKUP 函数有自己的参数做容错处理，更方便；

❸ LOOKUP 函数在多值判断时，需要升序排序，而 XLOOKUP 函数可以不用排序。

对了，如果你想系统性学习 Excel。

正好，我们秋叶家的《秋叶 Excel 3 天集训营》专为职场人准备，全部基于职场真实表格案例设计，还有很多超实用 Excel 技巧教学。

每天学习大概 30 分钟，从日常的功能出发，全程演示，一课一练，夯实进阶每一步。

秋叶 Excel 3 天集训营

原价 99 元

现在 只需 1 元

每天学习 30 分钟

你也有可能成为 Excel 高手！

现在就扫码报名吧！

👇👇👇

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

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

前面答应的 LOOKUP 的文章点这里👇👇👇

比Vlookup更厉害的查找之王，这个函数你知道吗？

今天就分享到这里。如果这篇文章对你有帮助，请帮忙「点赞」「在看」「转发」。

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

↓↓↓

遇到有价值的文章

点点在看支持一下 !

👇👇👇

动动小手

分享给朋友~

👇👇👇