📥 本章配套练习册（数据已录入、写完公式自动判分）：点击下载 第14章-XLOOKUP函数-练习.xlsx

难度：⭐⭐⭐ | 建议用时：40 分钟 | 前置知识：第13章 VLOOKUP ⚠️ 版本要求：仅 Microsoft 365 和 Excel 2021 及以上版本可用（Excel 2019 及更早版本没有此函数，请改用 VLOOKUP 或 INDEX+MATCH）

本章学习目标

• 会用 XLOOKUP 完成 VLOOKUP 能做的一切
• 掌握 XLOOKUP 的三大优势：任意方向查找、默认精确匹配、内置"找不到"提示
• 了解匹配模式参数做区间查找

一、这个函数是干什么的？

XLOOKUP 是微软 2020 年推出的"查找函数终极形态"，专门解决 VLOOKUP 的所有痛点：

二、语法结构

1

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

结构思维变了：不再是"一整张表 + 数列号"，而是"查找列和返回列两根柱子，平行对齐"。

三、基础示例（手把手）

商品价格表（与第13章相同）：

示例1：根据编码查名称

F2 输入 P003：

1

=XLOOKUP(F2, $A$2:$A$6, $B$2:$B$6)

结果：橙子

解读：在查找区域 A2:A6（编码列）里找 P003，找到第 4 行（相对位置 3），就把返回区域 B2:B6 里同样第 3 个的值（橙子）给你。 对比 VLOOKUP：不用数列号（选 B 列就是 B 列），不用写 FALSE（默认精确）。

示例2：内置"找不到"提示

1

=XLOOKUP(F2, $A$2:$A$6, $B$2:$B$6, "商品不存在")

F2 是 P009 时直接显示"商品不存在"——一个参数顶掉了整个 IFERROR 包装。

示例3：向左查找（VLOOKUP 做不到的事）

根据商品名称反查编码（名称在 B 列，编码在它左边的 A 列）：

1

=XLOOKUP(F2, $B$2:$B$6, $A$2:$A$6, "无此商品")

F2 输入"葡萄" → 结果 P004。

解读：查找区域选 B 列，返回区域选 A 列——方向无所谓，两根柱子对齐就行。VLOOKUP 遇到这种需求只能重组表格，XLOOKUP 一步到位。

四、进阶用法

1. 一次返回多列（365 专属，超好用）

1

=XLOOKUP(F2, $A$2:$A$6, $B$2:$D$6, "无此商品")

返回区域选了 3 列，结果会自动横向溢出：名称、单价、库存三个值一次填到三个格子。VLOOKUP 要写三个公式的事，它一个搞定。

2. 匹配模式做区间查找（不用排序！）

提成规则表（J 列下限 0/50000/100000/200000，K 列比例 3%/5%/8%/12%），业绩 86000：

1

=XLOOKUP(86000, $J$2:$J$5, $K$2:$K$5, 0, -1)

结果：5%

解读：匹配模式 -1 = “精确匹配，找不到就取下一个较小值"——50000 就是 86000 的"下一个较小"档。而且 XLOOKUP 的区间匹配不要求排序，又拆掉 VLOOKUP 一颗雷。

3. 从后往前找（搜索模式 -1）

一个商品多次出现在流水里，想找最后一次的记录：

1

=XLOOKUP(F2, A2:A100, B2:B100, "无", 0, -1)

搜索模式 -1 表示从区域底部往上找。

五、常见错误与注意事项

六、练习题（请先独立完成）

练习数据准备

商品价格表（A1:D6，同第13章）；F 列查询区：F2=P003、F3=葡萄（名称）、F4=P009。 提成规则表（J1:K5，同第13章）；M2=86000。

题目

七、练习讲解（做完再看）

第1题讲解

=XLOOKUP(F2,$A$2:$A$6,$B$2:$B$6) → 橙子。 注意两个区域起点都是第 2 行、长度都是 5 行——平行对齐是 XLOOKUP 的生命线。

第2题讲解

返回区域换成单价列：=XLOOKUP(F2,$A$2:$A$6,$C$2:$C$6) → 4.8。 对比 VLOOKUP 要改"列号 3"，XLOOKUP 直接换选 C 列，直观且不怕中间插入新列。

第3题讲解

向左查找：查找区域是名称列 B，返回区域是编码列 A：

1

=XLOOKUP(F3,$B$2:$B$6,$A$2:$A$6,"无此商品")

F3 = 葡萄 → P004。这是 XLOOKUP 相对 VLOOKUP 最直观的优势演示。

第4题讲解

=XLOOKUP(F4,$A$2:$A$6,$B$2:$B$6,"无此商品") → 无此商品。第4参数直接兜底，无需 IFERROR。

第5题讲解

=XLOOKUP(M2,$J$2:$J$5,$K$2:$K$5,0,-1) → 5%。 参数逐个看：查找值 M2；在下限列找；从比例列取；精确找不到时显示 0（兜底参数不能跳，因为匹配模式在第 5 位）；-1 = 取"精确或下一个较小值"。

第6题讲解

=XLOOKUP(F2,$A$2:$A$6,$B$2:$D$6,"无此商品") → 橙子、4.8、80 自动溢出到三个格子。 （如果显示 #SPILL!，说明溢出目标格子里有东西挡住了，清空右侧格子即可。）

第7题讲解

• VLOOKUP：=IFERROR(VLOOKUP(F2,$A$2:$D$6,2,FALSE),"无此商品")
• XLOOKUP：=XLOOKUP(F2,$A$2:$A$6,$B$2:$B$6,"无此商品")

XLOOKUP 省掉了：数列号、写 FALSE、套 IFERROR，还顺便解决了向左查找。版本允许时，优先 XLOOKUP。

八、参考答案

本章小结

• =XLOOKUP(查找值, 查找列, 返回列, [兜底], [匹配模式], [搜索模式])
• 三大优势：左右随便查、默认精确、自带"找不到"提示
• 查找列和返回列必须起点对齐、长度一致
• 匹配模式 -1 做区间查找，不要求排序
• 版本不够（2019 及更早）→ 用 VLOOKUP 或下一章的 INDEX+MATCH

下一章：第15章：MATCH 函数