XLOOKUP 反向查找和多条件:三个参数就够

员工表里工号排在姓名后面,用 VLOOKUP 查找姓名直接报 #N/A,还得插一列辅助列把顺序倒过来。

XLOOKUP 不管谁在左谁在右,找什么、在哪找、返回哪一列,三个参数就够,第四个参数顺手把错误值也处理了。

一、三个必填参数

标准写法:=XLOOKUP($A2,明细!$C:$C,明细!$A:$A,”无此人”)。第一个是要找的值,第二个是查找范围,第三个是返回范围。

返回范围可以和查找范围完全不在同一侧,也不用数列号,这就是反向查找不用辅助列的原因。

位置含义写法示例
第1个查找值$A2
第2个查找数组明细!$C:$C
第3个返回数组明细!$A:$A
第4个未找到返回值“无此人”
第5个匹配模式0 精确、2 通配符

二、反向查找的实操

选中 B2,输入公式后回车,向下填充即可。列宽不够会显示 #N/A 宽度不足的提示,拉宽列就好,不是公式错。

数据量超过十万行,把 C:C 换成 C2:C100000,速度会快一些。

查找列里如果有重复值,默认返回从上往下第一个匹配。想拿最后一次的记录,把第 6 个参数写 -1。

XLOOKUP反向查找|XLOOKUP 参数速查示意图
前三个必填,后面按需|Word联盟

三、多条件用连接符拼

两个条件一起匹配:=XLOOKUP($A2&”|”&$B2, 明细!$A:$A&”|”&明细!$B:$B, 明细!$D:$D)。中间的竖线是分隔符,避免“张三+1”和“张+三1”撞车。

这种写法是数组运算,365 和 2021 直接回车就出结果;2019 及更早不支持 XLOOKUP,别硬套。

场景公式写法
单条件反向XLOOKUP(A2, C:C, A:A)
双条件XLOOKUP(A2&”|”&B2, C:C&”|”&D:D, E:E)
按日期区间取价第5参数写 -1 取较小项
模糊包含第5参数写 2,查找值加 *

四、第 5、6 个参数解决两种麻烦

第 5 个参数管匹配方式:0 精确、-1 找下一个较小、1 找下一个较大、2 通配符。做阶梯价、税率表时用 -1,比嵌套 IF 干净。

第 6 个参数管方向:1 从前往后、-1 从后往前。订单表里同一个客户有多条记录,取最新一条就用 -1。

五、什么时候还是用 VLOOKUP

要给 2016 以前的版本打开、或者公司模板统一用 VLOOKUP,就还是 IfError 套 VLOOKUP,别为了新函数让文件打不开。

WPS 新版表格也支持 XLOOKUP,函数名一致,参数顺序一样,直接能用。

常见问题(FAQ)

为什么返回 #N/A?

查找列里没有这个值,或者两边格式不一致(一边文本一边数值)。先用 COUNTIF 数一下这个值在查找列里出现几次。

XLOOKUP 能整列引用吗?

可以,=XLOOKUP(A2,C:C,A:A)。数据量特别大时建议限定区域,避免每次重算全列。

WPS 里有这个函数吗?

新版 WPS 表格有,函数名和参数一致;老版本没有,会用 #NAME? 提示。

写在最后

XLOOKUP 的价值不在“新”,在省步骤:不用数列号、不用辅助列、不用另套 IFERROR。三个参数记牢,反向和多条件都能顺手解决。

唯一要留神的是版本,发给别人之前确认对方能不能打开。


类似文章