📖Excel XLOOKUP 函数使用指南

一篇覆盖入门到实战的 XLOOKUP 完整教程,告别 VLOOKUP 时代。

---

🔍 一、XLOOKUP 简介

XLOOKUP 是 Microsoft 在 2019 年底推出、随 Microsoft 365 与 Excel 2021 正式普及的新一代查找函数。它用一个函数同时取代了 VLOOKUP、HLOOKUP、LOOKUP 以及常用的 INDEX+MATCH 组合。

为什么推荐用 XLOOKUP:

• ✅ 支持向左、向右、向上、向下四个方向查找
• ✅ 默认精确匹配,避免 VLOOKUP 最易踩的近似匹配坑
• ✅ 内置 if_not_found 参数,告别嵌套 IFERROR
• ✅ 可从第一个或最后一个匹配项开始查找
• ✅ 原生支持返回多列 / 多行(动态数组自动溢出)
• ✅ 支持通配符和二分查找
• ✅ 插入列不会破坏公式(不靠列号定位)

---

📝 二、基本语法

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

参数	是否必填	说明
lookup_value	必填	要查找的值(可以是数字、文本、逻辑值或单元格引用)
lookup_array	必填	查找范围(一列或一行)
return_array	必填	返回范围(一列、一行,或多列/多行)
if_not_found	可选	未找到时返回的值,默认返回 #N/A
match_mode	可选	匹配模式,详见下表
search_mode	可选	搜索模式,详见下表

match_mode(匹配模式)

值	含义
0(默认)	精确匹配,找不到返回 #N/A 或 if_not_found
-1	精确匹配,找不到时返回下一个较小的项
1	精确匹配,找不到时返回下一个较大的项
2	通配符匹配(支持 *、?、~)

search_mode(搜索模式)

值	含义
1(默认)	从第一项开始顺序搜索
-1	从最后一项开始倒序搜索
2	升序数据的二分查找(需预先升序排序)
-2	降序数据的二分查找(需预先降序排序)

💡 二分查找在超大数据集(上万行)下性能显著优于顺序查找,但前提是数据已正确排序,否则会返回错误结果。

---

🎯 三、基础用法示例

示例数据

行\列	A	B	C	D
1	工号	姓名	部门	薪资
2	1001	张三	技术部	15000
3	1002	李四	销售部	12000
4	1003	王五	财务部	13000
5	1004	赵六	技术部	18000

1. 根据工号查姓名

=XLOOKUP(1003, A2:A5, B2:B5)

返回:王五

2. 反向查找(根据姓名查工号)

VLOOKUP 无法向左查找,但 XLOOKUP 可以:

=XLOOKUP("李四", B2:B5, A2:A5)

返回:1002

3. 未找到时返回自定义文本

=XLOOKUP(9999, A2:A5, B2:B5, "未找到该员工")

返回:未找到该员工

4. 水平方向查找(替代 HLOOKUP)

若数据按行排列,XLOOKUP 同样适用,只需把查找和返回范围改为行即可。

=XLOOKUP("Q3", B1:E1, B2:E2)

---

🚀 四、进阶用法

1. 返回多列数据(动态数组溢出)

一次返回姓名、部门、薪资:

=XLOOKUP(1002, A2:A5, B2:D5)

返回:李四 | 销售部 | 12000(自动溢出到横向三个单元格)

⚠️ 注意:溢出目标单元格必须为空,否则会提示 #SPILL! 错误。

2. 通配符查找

设置 match_mode = 2,可使用 *(任意多字符)、?(单个字符)、~(转义):

=XLOOKUP("张*", B2:B5, C2:C5, "未找到", 2)

返回:技术部

3. 从最后一条开始查找

当存在重复值时,查找最新的一条记录(search_mode = -1):

=XLOOKUP("技术部", C2:C5, B2:B5, , , -1)

返回:赵六(而非默认的 张三)

4. 区间查找(阶梯评级)

A(分数下限)	B(等级)
0	不及格
60	及格
75	良好
90	优秀

查找 82 分对应的等级(match_mode = -1 向下取最接近的较小值):

=XLOOKUP(82, A2:A5, B2:B5, , -1)

返回:良好

5. 多条件查找

通过连接符 & 实现多条件匹配:

=XLOOKUP("技术部"&"张三", C2:C5&B2:B5, D2:D5)

根据"部门+姓名"返回对应薪资。

💡 多条件时建议中间用分隔符避免歧义,如 "技术部|张三" 对应 C2:C5&"|"&B2:B5。

6. 二维查找(行列交叉)

嵌套两个 XLOOKUP 实现"既按行又按列"的查找,完全替代 INDEX+MATCH+MATCH:

=XLOOKUP("赵六", B2:B5, XLOOKUP("薪资", B1:D1, B2:D5))

• 内层:在表头中找到"薪资"列,返回整列
• 外层:在该列中按"赵六"定位行

返回:18000

7. 返回整行或整列

让 return_array 传入整表,结合布尔数组实现动态筛选:

=XLOOKUP(TRUE, (C2:C5="技术部")*(D2:D5>16000), B2:B5, "无匹配")

返回首个"技术部且薪资>16000"的姓名:赵六

---

💼 五、实战场景

场景 1:跨工作表引用

=XLOOKUP(A2, 员工信息!$A:$A, 员工信息!$D:$D, "查无此人")

将 A2 的工号在另一张工作表中查薪资,未找到时提示"查无此人"。

场景 2:跨工作簿引用

=XLOOKUP(A2, '[工资表.xlsx]Sheet1'!$A:$A, '[工资表.xlsx]Sheet1'!$D:$D)

⚠️ 关闭源工作簿后公式会显示完整路径,建议打开源文件或使用 Power Query 合并数据。

场景 3:动态数组批量查找

如果 lookup_value 本身是一个区域,XLOOKUP 会批量返回结果:

=XLOOKUP(F2:F10, A2:A100, D2:D100)

一次性为 F2:F10 中的所有工号返回薪资,无需下拉填充。

场景 4:查找并求和(与 SUM 组合)

查找"2024 年 1 月到 6 月"某销售员的总业绩:

=SUM(XLOOKUP("张三", A2:A100, B2:G100))

返回该员工 B~G 列(1~6 月)数据的总和。

场景 5:替代 IFNA / IFERROR 链式嵌套

=XLOOKUP(A2, 名单1!A:A, 名单1!B:B,
   XLOOKUP(A2, 名单2!A:A, 名单2!B:B, "均未找到"))

在"名单1"中找不到再去"名单2"找,避免 IFERROR(VLOOKUP(...), VLOOKUP(...)) 的冗长写法。

---

⚖️ 六、XLOOKUP vs VLOOKUP / INDEX+MATCH

维度	VLOOKUP	INDEX+MATCH	XLOOKUP
查找方向	仅向右	任意方向	任意方向
默认匹配	近似匹配(易错)	需手动指定	精确匹配
列号引用	数字索引(列插入易错)	列区域引用	列区域引用
错误处理	需嵌套 IFERROR	需嵌套 IFERROR	内置 if_not_found
反向 / 从尾查找	不支持	通过额外参数	原生支持
返回多列	不支持	需数组公式	动态数组溢出
通配符	支持	配合 MATCH 支持	支持
学习成本	低	高	低
可读性	中	低	高

结论:新版本 Excel 里,XLOOKUP 应当作为首选。

---

⚡ 七、性能与最佳实践

1. 不要引用整列做查找:A:A 会让 Excel 扫描上百万行,改为 A2:A10000 或使用"表"(Ctrl+T)动态扩展范围。
2. 大数据集使用二分查找:在数据已排序的前提下,search_mode = 2 / -2 比顺序扫描快一个数量级。
3. 避免易失性嵌套:少用 OFFSET、INDIRECT 搭配 XLOOKUP,会拖慢重算速度。
4. 结果可复用就缓存:同一个 XLOOKUP 在多处使用时,先在一个单元格算出,其他位置直接引用。
5. 使用表(Table):将数据区域转为 Excel 表后,公式自动扩展,维护更轻松。

---

🐛 八、常见错误与排查

错误值	常见原因	解决方式
#N/A	找不到匹配值	使用 if_not_found 参数,或检查数据类型是否一致
#VALUE!	查找范围与返回范围尺寸不一致	核对行数/列数是否相同
#SPILL!	动态数组溢出区域被占用	清空溢出目标单元格
#REF!	引用范围被删除或跨工作簿失效	检查引用路径
#NAME?	旧版本 Excel 不识别 XLOOKUP	升级到 Microsoft 365 / 2021

最常见坑:文本型数字 vs 数值型数字

=XLOOKUP(VALUE(A2), 数据!A:A, 数据!B:B)   // 查找值统一转数值
=XLOOKUP(A2&"", 数据!A:A, 数据!B:B)       // 查找值统一转文本

---

❓ 九、常见问题 FAQ

Q1:XLOOKUP 返回 #N/A?
检查查找值与查找列数据类型是否一致(例如文本型数字 vs 数值型数字),可用 VALUE() 或 TEXT() 统一格式;也可能是存在不可见空格,用 TRIM() 清理。

Q2:查找范围与返回范围必须同尺寸吗?
是的,两者行数或列数必须一致,否则返回 #VALUE!。

Q3:旧版本 Excel 能用吗?
XLOOKUP 仅支持 Microsoft 365 与 Excel 2021 及以上版本,Excel 2019 及更早版本打开会显示 _xlfn.XLOOKUP,需改用 VLOOKUP 或 INDEX+MATCH。

Q4:WPS 支持吗?
WPS Office 2023 及以上版本已支持 XLOOKUP,老版本需升级。

Q5:XLOOKUP 和 FILTER 有什么区别?
XLOOKUP 只返回第一个(或最后一个)匹配项,FILTER 返回所有匹配项。多结果场景用 FILTER,单值定位用 XLOOKUP。

Q6:查找值包含通配符字符本身怎么办?
使用 ~ 转义,例如查找真正的 * 就写 "~*",同时设置 match_mode = 2。

Q7:能否区分大小写?
XLOOKUP 默认不区分大小写。需要区分时可结合 EXACT:

=XLOOKUP(TRUE, EXACT(A2:A100, "Abc"), B2:B100)

---

✅ 十、小结

• 🎯 日常查找优先用 XLOOKUP 替代 VLOOKUP / INDEX+MATCH
• 📝 记住三个必填参数即可应对 80% 场景:查找值 / 查找区域 / 返回区域
• 🔧 结合 match_mode 与 search_mode 解决区间查找、最新记录、二分查找
• 🔗 多条件查找用 & 拼接,二维查找用 XLOOKUP 嵌套 XLOOKUP
• ⚡ 大数据集避免整列引用,已排序数据开启二分查找
• 🛡️ 内置 if_not_found,告别冗长的 IFERROR 嵌套

掌握 XLOOKUP,你的表格效率将提升一个量级。下一步可以继续学习 FILTER、SORT、UNIQUE 等动态数组函数,组合起来威力更大。