XLOOKUP – 智能查找函数
一、函数概述
XLOOKUP 函数是 Excel 中最强大的查找函数，用于在数组或区域中搜索匹配项并返回对应的值。它取代了 VLOOKUP 和 HLOOKUP，提供了更灵活、更强大的查找功能。
核心特性
- ✅ 支持向左查找（VLOOKUP 无法实现）
- ✅ 默认精确匹配，无需设置参数
- ✅ 内置错误处理功能
- ✅ 支持从后向前搜索
- ✅ 支持通配符匹配
二、函数语法和参数
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=XLOOKUP(查找值，查找数组，返回数组，[未找到时的返回值], [匹配模式], [搜索模式])
参数说明
- 查找值（必需）：要查找的值或单元格引用
- 查找数组（必需）：要搜索的单元格区域
- 返回数组（必需）：要返回结果的单元格区域
- 未找到时的返回值（可选）：未找到匹配时返回的值，默认为#N/A
- 匹配模式（可选）：0=精确匹配（默认），-1=精确匹配或下一个较小项，1=精确匹配或下一个较大项，2=通配符匹配
- 搜索模式（可选）：1=从第一个开始搜索（默认），-1=从最后一个开始搜索，2=二分法升序，-2=二分法降序
三、应用示例
3.1 精确匹配查询员工信息
场景说明：人力资源部门需要根据员工编号快速查找员工的详细信息。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 员工编号 | 姓名 | 部门 | 职位 | 入职日期 |
| 2 | EMP001 | 张三 | 销售部 | 销售经理 | 2022/3/15 |
| 3 | EMP002 | 李四 | 技术部 | 高级工程师 | 2021/7/1 |
| 4 | EMP003 | 王五 | 财务部 | 会计 | 2023/1/10 |
| 5 | EMP004 | 赵六 | 市场部 | 市场专员 | 2022/9/5 |
| 6 | EMP005 | 钱七 | 技术部 | 架构师 | 2020/5/20 |
❓ 问题：如何根据员工编号"EMP003"查找对应的姓名？
计算结果：王五
公式说明：在 A2:A6 区域中查找"EMP003"，找到后返回 B2:B6 区域中对应位置（第 4 行）的值"王五"。XLOOKUP 默认精确匹配，无需设置匹配模式参数。
3.2 向左查找员工编号
场景说明：已知员工姓名，需要反向查找员工编号（VLOOKUP 无法实现向左查找）。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 员工编号 | 姓名 | 部门 | 职位 | 入职日期 |
| 2 | EMP001 | 张三 | 销售部 | 销售经理 | 2022/3/15 |
| 3 | EMP002 | 李四 | 技术部 | 高级工程师 | 2021/7/1 |
| 4 | EMP003 | 王五 | 财务部 | 会计 | 2023/1/10 |
| 5 | EMP004 | 赵六 | 市场部 | 市场专员 | 2022/9/5 |
| 6 | EMP005 | 钱七 | 技术部 | 架构师 | 2020/5/20 |
❓ 问题：如何根据姓名"李四"查找对应的员工编号？
计算结果：EMP002
公式说明：在 B2:B6 区域中查找"李四"，找到后返回 A2:A6 区域中对应位置（第 3 行）的值"EMP002"。XLOOKUP 支持向左查找，这是 VLOOKUP 无法实现的功能。
3.3 返回多列信息（数组返回）
场景说明：查询员工信息时，需要一次性返回多个字段（部门、职位、入职日期）。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 员工编号 | 姓名 | 部门 | 职位 | 入职日期 |
| 2 | EMP001 | 张三 | 销售部 | 销售经理 | 2022/3/15 |
| 3 | EMP002 | 李四 | 技术部 | 高级工程师 | 2021/7/1 |
| 4 | EMP003 | 王五 | 财务部 | 会计 | 2023/1/10 |
| 5 | EMP004 | 赵六 | 市场部 | 市场专员 | 2022/9/5 |
| 6 | EMP005 | 钱七 | 技术部 | 架构师 | 2020/5/20 |
❓ 问题：如何根据员工编号"EMP004"一次性返回部门、职位和入职日期？
计算结果：市场部、市场专员、2022/9/5
公式说明：在 A2:A6 区域中查找"EMP004"，找到后返回 C2:E6 区域中对应位置（第 5 行）的三列数据：C5="市场部"，D5="市场专员"，E5="2022/9/5"。XLOOKUP 支持返回数组，可一次性获取多列信息。
3.4 自定义未找到时的提示信息
场景说明：查询产品信息时，如果产品不存在，需要显示友好的提示信息而非错误值。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 产品编号 | 产品名称 | 单价 | 库存量 | 供应商 |
| 2 | P001 | 笔记本电脑 | 6,500 | 50 | 联想 |
| 3 | P002 | 台式电脑 | 4,800 | 30 | 戴尔 |
| 4 | P003 | 打印机 | 1,500 | 20 | 惠普 |
| 5 | P004 | 扫描仪 | 800 | 15 | 佳能 |
| 6 | P005 | 投影仪 | 3,200 | 10 | 索尼 |
❓ 问题：如何根据产品编号"P008"查询库存量，如果产品不存在则显示"产品不存在"？
计算结果：产品不存在
公式说明：在 A2:A6 区域中查找"P008"，未找到匹配项时，返回第 4 个参数指定的文本"产品不存在"，而不是显示#N/A 错误值。这是 XLOOKUP 内置的错误处理功能。
3.5 通配符模糊查询
场景说明：客户名称只记得部分信息，需要使用通配符进行模糊匹配查询。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 客户编号 | 客户名称 | 联系人 | 电话 | 累计金额 |
| 2 | C001 | 华为科技有限公司 | 任总 | 0755-28888888 | 1,250,000 |
| 3 | C002 | 阿里巴巴集团 | 马总 | 0571-88888888 | 2,300,000 |
| 4 | C003 | 腾讯科技有限公司 | 马化腾 | 0755-86888888 | 1,800,000 |
| 5 | C004 | 百度网络技术有限公司 | 李彦宏 | 010-58888888 | 950,000 |
| 6 | C005 | 字节科技有限公司 | 张一鸣 | 010-68888888 | 1,100,000 |
❓ 问题：如何查找客户名称中包含"科技"的客户对应的联系人？
计算结果：任总
公式说明：在 B2:B6 区域中使用通配符"*科技*"进行查找，匹配包含"科技"的客户名称。第 5 个参数设置为 2 表示通配符匹配模式。第一个匹配项是 B2"华为科技有限公司"，返回对应 C2 的值"任总"。
3.6 从后向前搜索返回最后一个匹配项
场景说明：销售记录中同一产品有多条记录，需要获取最后一次的销售价格。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 销售日期 | 产品编号 | 产品名称 | 销售数量 | 销售单价 |
| 2 | 2024/1/5 | P001 | 笔记本电脑 | 10 | 6,500 |
| 3 | 2024/1/12 | P002 | 台式电脑 | 5 | 4,800 |
| 4 | 2024/1/18 | P001 | 笔记本电脑 | 8 | 6,400 |
| 5 | 2024/1/25 | P003 | 打印机 | 15 | 1,500 |
| 6 | 2024/2/1 | P001 | 笔记本电脑 | 12 | 6,300 |
❓ 问题：如何获取产品"P001"最后一次的销售单价？
计算结果：6,300
公式说明：在 B2:B6 区域中查找"P001"，第 5 个参数 0 表示精确匹配，第 6 个参数 -1 表示从后向前搜索。B6、B4、B2 都包含"P001"，从后向前第一个匹配是 B6，返回对应 E6 的值 6,300。
3.7 近似匹配查询折扣率
场景说明：根据采购数量确定折扣率，数量区间对应不同的折扣率。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 数量下限 | 数量上限 | 折扣率 | 适用产品 | 备注 |
| 2 | 1 | 49 | 0% | 全品类 | 无折扣 |
| 3 | 50 | 99 | 5% | 全品类 | 小额批发 |
| 4 | 100 | 499 | 10% | 全品类 | 中等批发 |
| 5 | 500 | 999 | 15% | 全品类 | 大额批发 |
| 6 | 1000 | 99999 | 20% | 全品类 | 超大额批发 |
❓ 问题：采购数量为 150 件时，对应的折扣率是多少？
计算结果：10%
公式说明：在 A2:A6 区域中查找 150，第 5 个参数 -1 表示精确匹配或下一个较小项。150 介于 100 和 499 之间，精确匹配失败后返回下一个较小项 100 对应的折扣率 C4=10%。
3.8 嵌套 XLOOKUP 实现多条件查询
场景说明：需要根据部门和员工姓名两个条件查询工资信息。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 部门 | 姓名 | 职位 | 基本工资 | 绩效奖金 |
| 2 | 销售部 | 张三 | 销售经理 | 15,000 | 8,000 |
| 3 | 技术部 | 李四 | 高级工程师 | 18,000 | 10,000 |
| 4 | 财务部 | 王五 | 会计 | 12,000 | 5,000 |
| 5 | 市场部 | 赵六 | 市场专员 | 10,000 | 6,000 |
| 6 | 技术部 | 钱七 | 架构师 | 25,000 | 15,000 |
❓ 问题：如何查询"技术部""李四"的基本工资？
计算结果：18,000
公式说明：在 B2:B6 区域中查找"李四"，找到后返回 D2:D6 区域中对应位置（第 3 行）的值 18,000。虽然本例只使用了一个条件，但 XLOOKUP 可以通过创建辅助列（如"部门&姓名"）或使用嵌套 XLOOKUP 实现真正的多条件查询。
四、注意事项
- ✅ XLOOKUP 仅在 Excel 2021 和 Office 365 中可用，旧版本不支持
- ✅ 查找数组和返回数组的行数/列数必须相同，否则返回#VALUE!错误
- ✅ 使用通配符匹配时，第 5 个参数必须设置为 2
- ✅ 从后向前搜索时，第 6 个参数设置为 -1
- ✅ XLOOKUP 默认精确匹配，无需像 VLOOKUP 那样设置 FALSE
- ✅ 返回数组可以是多列，实现一次性返回多个字段
五、总结
XLOOKUP 函数是 Excel 中最强大的查找函数，它集成了 VLOOKUP、HLOOKUP 的功能，并增加了向左查找、内置错误处理、从后向前搜索等高级特性。掌握 XLOOKUP 可以大幅提高工作效率，简化复杂的查找公式。
核心要点：XLOOKUP = 查找值 + 查找数组 + 返回数组 + 可选参数（错误提示、匹配模式、搜索模式），默认精确匹配，支持向左查找和数组返回。