30天Excel函数速成 · 第6天 / 共30天

██████░░░░ 6 / 30

📘今日掌握：XLOOKUP　｜　难度：⭐⭐ 进阶

前 5 天，我们一直在用 VLOOKUP 查数据。它确实是经典，但老职场人都被它坑过。

「表格中间插了一列，VLOOKUP 结果全错！」

「想根据姓名反查工号，VLOOKUP 根本查不了，因为它是从左往右找的！」

如果你用的是 Excel 365 或 WPS 新版，恭喜你，可以彻底摆脱 VLOOKUP 的这些毛病了。

今天第 6 天，我们学一个「降维打击」的函数：XLOOKUP。它是微软官方盖章的「VLOOKUP 继任者」。

一、今日公式

先记住它最常用的极简形态，只有三个参数：

=XLOOKUP(查找值, 查找数组, 返回数组)

放到工资表场景里，就是这一句：

=XLOOKUP(A2, 员工表!$B:$B, 员工表!$A:$A)

意思是：拿着工资表里的名字（A2），去员工表的 B 列（姓名列）里找，找到了，把对应的 A 列（工号列）给我返回过来。

注意：不需要数第几列，也不需要写 0（默认就是精确匹配）。这两个坑，VLOOKUP 每天都要带走一批人。

二、XLOOKUP 吊打 VLOOKUP 的 3 个神技

神技 1 ｜ 反向查找：从左往右、从右往左随便查

VLOOKUP 痛点：只能从左往右查。想根据「姓名」查左边一列的「工号」，必须把原表列顺序打乱。

XLOOKUP 玩法：查找列和返回列随便放。上一节的例子里，查找数组＝姓名列，返回数组＝工号列，就这么简单。

神技 2 ｜ 不怕插列删列

VLOOKUP 痛点：原来查的是第 2 列，同事在中间插了一列变成第 3 列，公式瞬间报废，全表报错。

XLOOKUP 玩法：直接选中「要返回哪一列的区域」，插列删列它自动跟着走，绝不会错位。

神技 3 ｜ 自带防错兜底，IFERROR 都省了

VLOOKUP 痛点：找不到数据时返回一堆 #N/A，还得在外层套一个 IFERROR。

XLOOKUP 玩法：它支持第 4 个参数，专门用来填「找不到时显示什么」：

=XLOOKUP(A2, 员工表!$B:$B, 员工表!$A:$A, "查无此人")

三、真实案例：一张表把 4 个信息全查出来

假设你有两张表：员工表（工号、姓名、部门、岗位）和 工资表（工号、部门、姓名、实发工资）。

员工表长这样：姓名在 B 列，工号在 A 列——注意，工号在姓名的左边，这正是 VLOOKUP 查不了的场景

现在只要在查询格子里填一个名字，工号、部门、岗位、实发工资四行全部自动出来：

画面重点：改 B3 的姓名，下面四行结果跟着变；底部「查无此人」是第 4 参数兜底

四条公式长得几乎一样，只有返回列不同：

=XLOOKUP($B$3, 员工表!$B:$B, 员工表!$A:$A, "查无此人")

=XLOOKUP($B$3, 员工表!$B:$B, 员工表!$C:$C, "查无此人")

=XLOOKUP($B$3, 员工表!$B:$B, 员工表!$D:$D, "查无此人")

=XLOOKUP($B$3, 工资表!$C:$C, 工资表!$F:$F, "查无此人")

再进阶一步：多条件查找。老板问「财务部工号 CW-001 的人，这个月发了多少」，用 & 把两个条件拼成一个整体去匹配：

=XLOOKUP(B12&D12, 工资表!$B:$B&工资表!$A:$A, 工资表!$F:$F)

意思是：把「财务部」和「CW-001」连起来当成一个字符串，去和「工资表里部门列＋工号列」的拼接结果比对。换成 VLOOKUP，还得先建一列辅助列，XLOOKUP 一行就搞定。

自检方法：多条件查出来的实发工资，应该和上面单条件查出来的完全一样（都是 15,200）。如果对不上，说明拼接的列顺序错了——查找条件的顺序必须和返回区域里列的顺序一致。

四、避坑提醒

① 版本限制：只有 Office 365、Excel 2021 以及最新版 WPS 才有这个函数。公司老电脑的 Excel 2016 会直接提示「无效名称」，看到 #NAME? 就是这个原因。老版本请继续用 VLOOKUP，或者用明天要讲的 INDEX+MATCH。

② 名字对不上就是查不到：查找值多一个空格、少一个字，都匹配不上。默认走精确匹配，所以会走你设置的第 4 参数（显示「查无此人」）。如果一片都是「查无此人」，先检查是不是原文里带空格。

③ 返回数组别多选：你只要 H 列，就别把 H 到 L 列一起选上。XLOOKUP 会把整块返回，形成「溢出数组」，把旁边有内容的格子直接盖掉，报 #SPILL!。

学会 XLOOKUP 后你才发现，
以前为了 VLOOKUP 绕的那些弯路，
都是时代留下的眼泪。

今日练习

打开你自己的表格，用 XLOOKUP 实现一次「根据姓名反查工号」。再做一遍多条件查找。做完截图发到后台，明天抽一位读，帮你检查公式。

明日预告

第 7 天：INDEX + MATCH —— 如果你的 Excel 版本不支持 XLOOKUP，这套组合拳就是最强平替，反向查找一样能做到。

← 上一章：第5天 数据验证进阶，下拉菜单 + 联动 + 防错

下一章：第7天 INDEX + MATCH，XLOOKUP 的最强平替 →