Excel跨表查找只需1秒:XLOOKUP函数详解

Excel跨表查找只需1秒：XLOOKUP函数详解

上一期我们讲了 VLOOKUP，很多同学留言说：

"VLOOKUP 只能从左往右查，查找值必须在第一列，太麻烦了！"

没错，这正是 VLOOKUP 的硬伤。

今天介绍它的升级替代版——XLOOKUP。

微软在 2019 年推出 XLOOKUP，专门解决 VLOOKUP 的所有痛点。用过的人都说：再也不想回去用 VLOOKUP 了。

XLOOKUP vs VLOOKUP：一眼看懂区别

对比项	VLOOKUP	XLOOKUP
查找方向	只能向右	任意方向（左右均可）
查找值位置	必须在区域第一列	任意列
找不到时	报 #N/A 错误	可自定义返回值
返回多列	需要多个公式	一个公式返回多列
参数数量	4个（第4个易混淆）	3个必填，3个可选，更直观
版本要求	所有版本	Microsoft 365 / Excel 2021+

XLOOKUP 基础语法

=XLOOKUP(查找值, 查找区域, 返回区域, [找不到时返回], [匹配模式], [搜索模式])

必填参数（前3个）：

参数	含义	示例
查找值	你要找什么	B2（客户ID）
查找区域	在哪一列找	客户!A:A（客户ID列）
返回区域	找到后返回哪一列	客户!B:B（公司名称列）

可选参数（后3个）：

参数	含义	常用值
找不到时返回	自定义未找到的提示	"未找到"
匹配模式	精确/近似/通配符	0=精确（默认）
搜索模式	从头/从尾/二分查找	1=从头（默认）

场景一：基础跨表查找

需求： 在订单表中，根据客户ID查出公司名称。

=XLOOKUP(B2, 客户!A:A, 客户!B:B)

对比 VLOOKUP：

=VLOOKUP(B2, 客户!A:C, 2, 0)   ← 需要数列号，容易数错=XLOOKUP(B2, 客户!A:A, 客户!B:B)  ← 直接指定返回列，一目了然

XLOOKUP 的优势：返回区域直接指定列，不用数第几列，不会数错！

场景二：向左查找（VLOOKUP 做不到！）

这是 XLOOKUP 最亮眼的特性。

需求： 已知公司名称，反查客户ID。

VLOOKUP 无法实现（查找值必须在最左列），但 XLOOKUP 轻松搞定：

=XLOOKUP(D2, 客户!B:B, 客户!A:A)

查找区域：B列（公司名称）
返回区域：A列（客户ID）

向左查找，完全没问题！

场景三：找不到时自定义返回值

VLOOKUP 找不到数据时会显示难看的 #N/A，需要额外套 IFERROR。

XLOOKUP 内置了这个功能，第4个参数直接设置：

=XLOOKUP(B2, 客户!A:A, 客户!B:B, "客户不存在")

找不到时自动显示"客户不存在"，干净利落。

场景四：一次返回多列数据

需求： 根据客户ID，同时查出公司名称、联系电话、所在城市。

VLOOKUP 需要写3个公式：

=VLOOKUP(B2, 客户!A:D, 2, 0)   ← 公司名称=VLOOKUP(B2, 客户!A:D, 3, 0)   ← 联系电话=VLOOKUP(B2, 客户!A:D, 4, 0)   ← 所在城市

XLOOKUP 一个公式搞定（选中3个单元格后输入）：

=XLOOKUP(B2, 客户!A:A, 客户!B:D)

返回区域选 B:D（3列），公式自动溢出填充3列数据！

场景五：近似匹配（区间判断）

和 VLOOKUP 一样，XLOOKUP 也支持近似匹配，用于区间判断。

需求： 根据销售额判断等级。

=XLOOKUP(B2, 等级!A:A, 等级!B:B, "无数据", 1)

第5个参数 1 = 精确匹配或下一个较小值（等同于 VLOOKUP 的近似匹配）。

匹配模式参数说明：

值	含义
0	精确匹配（默认）
-1	精确匹配或下一个较小值
1	精确匹配或下一个较大值
2	通配符匹配（支持 * 和 ?）

场景六：通配符模糊查找

XLOOKUP 支持通配符，这是 VLOOKUP 不具备的！

需求： 查找名称中包含"华联"的公司。

=XLOOKUP("*华联*", 客户!B:B, 客户!A:A, "未找到", 2)

第5个参数 2 = 通配符模式，* 代表任意字符。

场景七：从后往前查找（返回最后一条）

当有重复数据时，VLOOKUP 只能返回第一条匹配结果。

XLOOKUP 通过第6个参数控制搜索方向：

=XLOOKUP(B2, 订单!A:A, 订单!C:C, "无记录", 0, -1)

第6个参数 -1 = 从最后一行往前搜索，返回最新的一条记录。

搜索模式参数说明：

值	含义
1	从头到尾（默认）
-1	从尾到头（返回最后一条）
2	二分查找（升序排列时用，速度更快）
-2	二分查找（降序排列时用）

XLOOKUP 常见错误及解决

错误	原因	解决方法
#N/A	查找值不存在	添加第4参数：`"未找到"`
#VALUE!	查找区域与返回区域行数不一致	确保两个区域行数相同
#SPILL!	溢出区域有数据阻挡	清空返回区域右侧的单元格
版本不支持	Excel 版本过低	需要 Microsoft 365 或 Excel 2021+

XLOOKUP 公式速查

场景	公式
基础跨表查找	`=XLOOKUP(B2, 客户!A:A, 客户!B:B)`
带默认值	`=XLOOKUP(B2, 客户!A:A, 客户!B:B, "未找到")`
向左查找	`=XLOOKUP(D2, 客户!B:B, 客户!A:A)`
返回多列	`=XLOOKUP(B2, 客户!A:A, 客户!B:D)`
近似匹配	`=XLOOKUP(B2, 等级!A:A, 等级!B:B, "", -1)`
通配符查找	`=XLOOKUP("关键词", 区域, 返回区域, "未找到", 2)`
返回最后一条	`=XLOOKUP(B2, 区域, 返回区域, "", 0, -1)`

总结

XLOOKUP 是 VLOOKUP 的全面升级版：

✅ 不限方向：左右均可查找
✅ 不限位置：查找值可在任意列
✅ 内置防错：第4参数直接处理找不到的情况
✅ 一次多列：一个公式返回多列数据
✅ 通配符：支持模糊匹配
✅ 反向搜索：可返回最后一条匹配记录

唯一限制： 需要 Microsoft 365 或 Excel 2021 及以上版本。

如果你的 Excel 版本支持，从今天起就用 XLOOKUP 替代 VLOOKUP 吧！

往期推荐：

Excel跨表查找只需1秒：VLOOKUP函数详解
5个Excel快捷键，让你效率翻倍
数据透视表入门：3分钟搞定数据分析

关注公众号，持续分享职场Excel干货！