员工表里工号排在姓名后面,用 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($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。三个参数记牢,反向和多条件都能顺手解决。
唯一要留神的是版本,发给别人之前确认对方能不能打开。

