点击蓝字【秋叶 Excel】
发送【交流】
立即进【秋叶同学会】交流 Excel!
![](http://dingyue.ws.126.net/2023/0105/40fdae91j00rnzu08001vd200u0008xg00hx005b.jpg)
本文作者:赵骄阳
本文编辑:竺兰
Hi,我是偏爱函数公式,爱用 Excel 图表管理仓库的大叔 Mr 赵~
前段时间秋叶 Excel 发了篇讲 LOOKUP 的文章 (链接见文末) ,有同学留言问为什么不讲XLOOKUP函数?
![](http://dingyue.ws.126.net/2023/0105/637cdc32p00rnzu080007d2008q0023g008q0023.png)
我觉得可以!
所以我们今天就来讲讲 XLOOKUP,一个强大的查询函数!
在说 XLOOKUP 函数之前,咱们先看看下面这个案例,根据姓名查找对应的性别。
![](http://dingyue.ws.126.net/2023/0105/3b737852j00rnzu09000cd200fg005kg00g2005r.jpg)
这个问题太常见了!首先想到的就是用 VLOOKUP 函数。
=VLOOKUP(E2,A2:C7,2,0)
在查找区域 A2:C7 的首列找到 E2 单元格的值「王五」,返回查找区域 A2:C7 第 2 列与之对应的值「男」。
![](http://dingyue.ws.126.net/2023/0105/86059b9dj00rnzu09000id200fb006ag00g2006l.jpg)
![](http://dingyue.ws.126.net/2023/0105/6fbb4c19j00rnzu0a0005d200ku007cg00hx006b.jpg)
初识 XLOOKUP 函数
再来看看 XLOOKUP 函数的用法:
=XLOOKUP(查找的值,查找范围,结果范围)
公式就可以这样写:
=XLOOKUP(E2,A2:A7,B2:B7)
在查找范围 A2:A7 中找到 E2 单元格的值「王五」,返回 B2:B7 对应的值「男」。
![](http://dingyue.ws.126.net/2023/0105/79fd4d1cj00rnzu0b000id200f8006bg00g2006n.jpg)
如果姓名这列不是在前面,用 VLOOKUP 函数似乎就不太合适了。因为 VLOOKUP 函数的规则是在查找区域的首列查找。
这种情况下,我们通常会用INDEX 函数和 MATCH 函数组合写公式:
=INDEX(A2:A7,MATCH(E2,B2:B7,0))
MATCH 函数找出 E2 的值「王五」在 B2:B7 中是第几行,得到结果 3,然后用 INDEX 函数将 A2:A7 的第 3 行的值引用出来,得到结果「男」。
![](http://dingyue.ws.126.net/2023/0105/a18ca688j00rnzu0b000hd200fe006cg00g2006l.jpg)
而 XLOOKUP 函数就不一样了,它不会像 VLOOKUP 那样受位置的影响,依然照用不误:
=XLOOKUP(E2,B2:B7,A2:A7)
在查找区域 B2:B7 中找到 E2 单元格的值「王五」,返回 A2:A7 对应的值「男」。
![](http://dingyue.ws.126.net/2023/0105/00d6e4ecj00rnzu0b000id200fd0066g00fk0068.jpg)
![](http://dingyue.ws.126.net/2023/0105/43349d15j00rnzu0c0006d200ku007cg00hx006b.jpg)
XLOOKUP 函数的第 4 参数
经常有小伙伴提这样的问题,如何让 VLOOKUP 查找不到的数据返回为空值?
如下图,VLOOKUP 函数在查找区域 A2:B7 的首列没有找到单元格的值「孙二」,就会返回错误值#N/A。
=VLOOKUP(D2,A2:B7,2,0)
![](http://dingyue.ws.126.net/2023/0105/3eaf6e0bj00rnzu0c000hd200ew006lg00g20073.jpg)
通常我们都会在 VLOOKUP 函数外层嵌套 IFERROR 函数,或者用 IFNA 函数来容错。
=IFNA(VLOOKUP(D2,A2:B7,2,0),"")
![](http://dingyue.ws.126.net/2023/0105/9fc02408j00rnzu0d000fd200eu005sg00g20069.jpg)
而 XLOOKUP 函数有它专属的参数:它的第 4 个参数专门负责容错。
=XLOOKUP(查找值,查找范围,返回范围,[容错])
这个参数是非必需参数,当你碰到上面这种问题,才有必要把它请出来。
公式就可以写成:
=XLOOKUP(D2,A2:A7,B2:B7,"")
![](http://dingyue.ws.126.net/2023/0105/c816feffj00rnzu0d000gd200es005wg00g2006e.jpg)
在查找范围 A2:A7 中找 E2 单元格的值「孙二」,如果有找到就返回 B2:B7 对应的值,如果没有找到,则返回第 4 参数指定的内容「""」。
当然第 4 参数的设定并非只可以是字符串,数值。也可以嵌套其它的公式返回结果。
![](http://dingyue.ws.126.net/2023/0105/0079a88ej00rnzu0e0006d200ku007cg00hx006b.jpg)
XLOOKUP 函数的第 5 参数
早前有一篇根据得分评定等级的文章:
用 XLOOKUP 函数同样可以轻松解决。
以下是评定的规则:
小于 60 分不合格;
大于等于 60 小于 70 为合格;
大于等于 70 小于 80 为良好;
大于等于 80 为优秀。
先为每个等级设置分数的下限,如下图 A 列,然后在 E2 单元格写入公式:
=XLOOKUP(D2,A2:A5,B2:B5,,-1)
![](http://dingyue.ws.126.net/2023/0105/c9b4558ej00rnzu0f000fd200ff005bg00g2005i.jpg)
XLOOKUP 函数的第 5 参数是匹配类型。
=XLOOKUP(查找值,查找范围,返回范围,[容错], [匹配类型])
当第 5 参数的值为-1 时,表示如果「查找值」没有在「查找范围」中,就返回下一个较小的值。
如上面的公式中,D2 单元格的值是 75,没有在查找区域 A2:A7 中,就找比 75 小的值,即 70。再返回 B2:B5 对应的等级「良好」。
如果把 A 列的分数下限改成上限,公式就可以这样写:
=XLOOKUP(D2,A2:A5,B2:B5,,1)
![](http://dingyue.ws.126.net/2023/0105/e5628e27j00rnzu0q000gc000f7005gc.jpg)
当第 5 参数为 1 时,表示如果「查找值」没有在「查找范围」中,就返回下一个较大的值。
如公式中,D2 单元格的值是 75,没有在查找区域 A2:A7 中,就找比 75 大的值,即 79。再返回 B2:B5 对应的等级「良好」。
![](http://dingyue.ws.126.net/2023/0105/7b45a295j00rnzu0q0005c000ku007cc.jpg)
写到最后
关于 VLOOKUP、XLOOKUP 和 LOOKUP 有哪些区别,以下 Tips 供大家参考:
❶ VLOOKUP 函数必须在查找区域的首列查找,而 XLOOKUP 函数不受这种位置限制;
❷ VLOOKUP 函数需要其它函数嵌套来容错,而 XLOOKUP 函数有自己的参数做容错处理,更方便;
❸ LOOKUP 函数在多值判断时,需要升序排序,而 XLOOKUP 函数可以不用排序。
对了,如果你想系统性学习 Excel。
正好,我们秋叶家的《秋叶 Excel 3 天集训营》专为职场人准备,全部基于职场真实表格案例设计,还有很多超实用 Excel 技巧教学。
每天学习大概30 分钟,从日常的功能出发,全程演示,一课一练,夯实进阶每一步。
秋叶 Excel 3 天集训营
每天学习 30 分钟
你也有可能成为 Excel 高手!
现在就扫码报名吧!
▲ 报名成功后将自动弹出班主任二维码,切勿提前退出
若未能添加,请联系公众号客服获取
前面答应的 LOOKUP 的文章点这里
今天就分享到这里。如果这篇文章对你有帮助,请帮忙「点赞」「在看」「转发」。
这对我很重要,能给我更多动力,持续分享优质的内容。
![](http://dingyue.ws.126.net/2023/0105/8fb42557j00rnzu0m002vd200u00076g00f6003m.jpg)
遇到有价值的文章
点点在看支持一下 !
动动小手
分享给朋友~
热门跟贴