Redian新闻
>
比Vlookup更逆天!这个强大的查询函数,看完我就跪了!

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

公众号新闻

点击【秋叶 Excel】👆发送【插件】

免费领 Excel 必备 4 大插件


作者:赵骄阳
编辑:竺兰


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

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


我觉得可以!

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

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


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

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

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


现在关注【秋叶 Excel】

回复关键词【手册】

就能免费获取 40+ Excel函数手册

👇👇👇

初识 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更厉害的查找之王,这个函数你知道吗?


今天就分享到这里。如果这篇文章对你有帮助,请帮忙「点赞」「在看」「转发」
 
这对我很重要,能给我更多动力,持续分享优质的内容。



关注公众号【秋叶 Excel】
回复关键词礼包
还能免费领办公神器
Office 模板和免商字体!
↓↓↓

微信扫码关注该文公众号作者

戳这里提交新闻线索和高质量文章给我们。
相关阅读
字节跳动开源 kube-apiserver 高可用方案 KubeGateway高颜值包装,浓浓奶香味,日本Yoku Moku点心6折!吃一个就完全停不下来!这400条职场加分好习惯,看完我想打印出来太高级了!你没见过的Yoku Moku限定礼盒,情人节甜品就选它们啦~真的每个都想试!安徽女子明码标价:5000万起步,看完我沉默了...上新了故宫又来搞事?全场低至139,看完我默默下单了小米A/B实验场景基于Apache Doris的查询提速优化实践【Locker Room】LOL联赛?篮球全明星?Lockerroom有什么新活?被师兄爆赞的综述教程,看完我反手发表了一篇综述……小区里的“狼嚎”退休老人的福利还是梦魇如何用 Kubeadm 在 Debian 11 上安装 Kubernetes 集群 | Linux 中国业界首个适用于固体系统的神经网络波函数,登上Nature子刊再见Vlookup!这5个数据核对技巧,太有用了!老板发来一个「私藏小视频」,看完我直接呆了...这个Excel高手常用的随机函数,太牛X了!8万股民惊呆了!收问询函后,突击减持,3倍大牛股崩了……马里奥“超级达摩”:SUPER强大 x SUPER好运!交易虚函数,C++开发者如何有效利用?搬家,却不入住刚刚!高盛小摩2023年目标校名单流出,看完我人麻了…30秒教你学会Vlookup函数,简单到哭哭!全联的泡面文案,看完我悟了讀李敖《北京法源寺》鹅乌最新:不义之战打碎了沙鹅梦,打出了一个强大的乌克兰海投组合丨印尼金融科技独角兽Akulaku再融2亿美元跪了!打磨23年,给孩子的「上下五千年」要上天!如何搭建一个强大的数据预警模型?比Vegas还要纸醉金迷的度假胜地,洛杉矶开车一小时就能到VLOG| HUSKY的一天!看完美国卡车司机的vlog,这帮人馋哭了?这双“情侣鞋”太太太太甜了!看完我都想恋爱了!一款功能强大的 IP 查询工具!开源,放心用!2022女生体重标准又变了!看完我真的慌了!
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。