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])

match_mode(匹配模式)

search_mode(搜索模式)

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

🎯 三、基础用法示例

示例数据

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

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

⚡ 七、性能与最佳实践

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

🐛 八、常见错误与排查

最常见坑:文本型数字 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 等动态数组函数,组合起来威力更大。