EP13. “XLOOKUP Function XLOOKUP 函数”
🔒 登录后可标记已读- XLOOKUP 是 Excel 365 / 2021 才有的新一代查找函数
- 功能比 VLOOKUP 更全面：默认精确匹配、支持向左查找、可以一次返回多列、支持从后往前找
- 前置知识建议先看过 EP01 的 VLOOKUP，方便对照差异
- 学完能理解为什么官方建议新版本用户直接改用 XLOOKUP
重点内容
函数速查
| 函数 | 语法 | 用途 | 例子 |
|---|---|---|---|
| XLOOKUP | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | 新一代查找函数，精确匹配为默认，支持双向查找 | =XLOOKUP(53,B3:B9,E3:E9) |
VLOOKUP 和 XLOOKUP 怎么选
| 场景 | VLOOKUP | XLOOKUP |
|---|---|---|
| 默认匹配方式 | 需要手动指定 FALSE 才是精确匹配 | 默认就是精确匹配 |
| 向左查找 | 做不到，要绕道 INDEX+MATCH | 天生支持 |
| 找不到时的提示 | 要额外包一层 IFNA | 内建第四参数直接指定 |
| 一次返回多列 | 做不到 | 支持，自动 spilling |
| 版本要求 | 桌面版通用 | 仅 Excel 365 / 2021 及以上 |
精确匹配（默认行为）
=XLOOKUP(53,B3:B9,E3:E9)
在 B3:B9 查找 53，从 E3:E9 返回对应值——不用像 VLOOKUP 那样额外指定第四参数为 FALSE，XLOOKUP 默认就是精确匹配。 另一个例子：=XLOOKUP(79,D3:D9,B3:B9)，按 ID 79 查姓氏。
找不到时的处理
默认找不到会返回 #N/A（比如查找值 28 在 B3:B9 里不存在）。XLOOKUP 内建了第四参数可以直接指定找不到时显示什么，不用再额外包 IFNA：
=XLOOKUP(28,B3:B9,E3:E9,"未找到")
近似匹配
第五参数 match_mode 可以设为 -1（找下一个较小值）或 1（找下一个较大值），不需要像 VLOOKUP 那样必须先排序数据：
=XLOOKUP(85,B3:B7,E3:E7,,-1) → 找到次小值 80
=XLOOKUP(85,B3:B7,E3:E7,,1) → 找到次大值 90
向左查找
XLOOKUP 没有"只能向右"的限制，直接可以按姓氏反查 ID，不需要像 VLOOKUP 那样绕道用 INDEX+MATCH（对比 EP07）。
一次返回多列（多值返回）
把 return_array 参数从单列范围扩大成多列范围（比如从 C6:C12 扩大到 C6:E12），一条公式就能同时返回名字、姓氏、薪资三列数据，自动铺开到相邻单元格，这种自动铺开的行为叫 spilling（溢出）。
[截图：一条 XLOOKUP 公式返回多列后，结果自动溢出铺满相邻单元格的实际效果]
水平查找
XLOOKUP 同样能做水平方向的查找，取代传统的 HLOOKUP 函数。
反向搜索（找最后一个匹配）
第六参数 search_mode 设为 -1，代表从数据末尾往前搜索（而不是默认从头开始）：
=XLOOKUP("Mia Reed",B3:B9,E3:E9,,0,-1)
如果有多个同名 "Mia"（比如 Mia Clark 和 Mia Reed 都叫 Mia），设成 -1 可以找到最后一个匹配到的那笔（Mia Reed 的薪资），而不是第一个。
学完你会
- ✅ 能用 XLOOKUP 做默认精确匹配的查找，不用再额外指定第四参数
- ✅ 能用 XLOOKUP 直接向左查找，不用绕道 INDEX+MATCH
- ✅ 能用 XLOOKUP 一次返回多列数据，或反向搜索找最后一个匹配
常见错误
- XLOOKUP 只在 Excel 365 / Excel 2021 及之后版本可用，拿旧版本 Excel 打开会直接报错，分享文件给用旧版本同事时要注意
- 忘记 XLOOKUP 默认就是精确匹配，习惯了 VLOOKUP 还去多此一举地额外指定精确匹配参数
- 多值返回时忘记右边相邻单元格要留空，否则 spilling 会被占用的单元格挡住，报
#SPILL!错误
Sources
Blog / Website：