Excel XLOOKUP 函数使用指南
- 2026-05-19 18:18:11
一篇覆盖入门到实战的 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 | 降序 |
💡 二分查找在超大数据集(上万行)下性能显著优于顺序查找,但前提是数据已正确排序,否则会返回错误结果。
🎯 三、基础用法示例
示例数据
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. 区间查找(阶梯评级)
查找 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))返回该员工 BG 列(16 月)数据的总和。
场景 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
| XLOOKUP | |||
| 精确匹配 | |||
| 内置 if_not_found | |||
| 原生支持 | |||
| 动态数组溢出 | |||
| 低 | |||
| 高 |
结论:新版本 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? |
最常见坑:文本型数字 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 等动态数组函数,组合起来威力更大。
