Excel XLOOKUP函数教程：9个XLOOKUP案例详解与VLOOKUP区别¶
在 Excel 日常数据处理中，Excel XLOOKUP函数 已经成为非常实用的现代查找方案。很多人学习 XLOOKUP教程 时，最关心的往往是函数语法怎么写、常见 XLOOKUP案例 应该如何落地，以及 XLOOKUP和VLOOKUP区别 到底体现在哪些场景中。
XLOOKUP 是 Microsoft Excel 中一个功能强大的现代查找函数，于 2019 年随 Office 365 推出，旨在替代传统的 VLOOKUP 和 HLOOKUP。它既能完成最基础的精确匹配，也能处理横向查找、反向查找、批量返回、多条件查找、倒序查找和近似匹配等场景。相比传统函数，它的写法更直观，逻辑也更清晰。
如果你正在做销售数据匹配、员工信息查询、提成区间判断，或者想减少复杂公式的嵌套，那么这篇文章中的 9 个案例基本可以覆盖大多数常见需求。
XLOOKUP 基本语法¶
XLOOKUP 的完整语法如下：
=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 |
匹配模式 | 可选 | 0 为精确匹配（默认），-1 为精确匹配或返回下一个更小值，1 为精确匹配或返回下一个更大值 |
search_mode |
搜索模式 | 可选 | 1 为从前到后查找（默认），-1 为从后到前查找 |
与 VLOOKUP/HLOOKUP 的核心区别¶
XLOOKUP 之所以更适合现代 Excel 使用场景，很大程度上是因为它把过去分散在多个函数里的能力整合到了一起。
| 对比维度 | VLOOKUP/HLOOKUP | XLOOKUP |
|---|---|---|
| 查找方向 | VLOOKUP 主要处理竖向查找，HLOOKUP 处理横向查找 | 一个函数同时支持竖向和横向查找 |
| 返回位置限制 | VLOOKUP 通常要求返回列位于查找列右侧 | 返回区域不受左右限制，可直接反向查找 |
| 默认匹配方式 | VLOOKUP 容易因参数设置不当产生近似匹配问题 | 默认就是精确匹配，更稳妥 |
| 返回结果形式 | 通常返回单个值 | 支持返回整行、整列或多列结果 |
| 错误处理 | 往往需要搭配 IFERROR() |
可直接使用 if_not_found 参数 |
| 高级查找 | 倒序查找、多条件查找需要额外技巧 | 通过参数与数组能力可以直接完成 |
9 个典型案例详解¶
案例一、竖向查找¶
使用场景¶
在一列数据中，根据姓名查找对应的销售额。这是 XLOOKUP 最基础、也是最常见的用法。
图片¶
查找目标¶
查找小陈的销售额，结果为 60。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
F2 |
查找值，即“小陈”所在单元格 |
lookup_array |
A2:A13 |
姓名列，也就是竖向查找范围 |
return_array |
B2:B13 |
销售额列，返回对应结果 |
结果说明¶
XLOOKUP 会在 A2:A13 中从上到下查找“小陈”，找到后返回同一行 B2:B13 中的值，也就是 60。
核心知识点或实战价值¶
这个案例说明，XLOOKUP 可以直接指定“查找列”和“返回列”，不需要像 VLOOKUP 那样再去数第几列，公式更直观，也更不容易出错。
案例二、横向查找¶
使用场景¶
当数据是横向排列时，比如姓名在第一行、销售额在第二行，就可以用 XLOOKUP 直接做横向查找。
图片¶
查找目标¶
查找小陈的销售额，结果为 60。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
A23 |
查找值“小陈” |
lookup_array |
B18:M18 |
姓名所在的横向一行 |
return_array |
B19:M19 |
销售额所在的横向一行 |
结果说明¶
函数会在第一行中定位“小陈”，然后返回第二行同一列对应的销售额 60。
核心知识点或实战价值¶
这正是 XLOOKUP 相比 VLOOKUP 的明显优势之一。VLOOKUP 更适合竖向查找，横向通常要换成 HLOOKUP；而 XLOOKUP 用同一套语法就能同时覆盖横向与竖向场景。
案例三、反向查找¶
使用场景¶
当返回列位于查找列左侧时，传统 VLOOKUP 会比较受限，而 XLOOKUP 可以直接完成这种“反向查找”。
图片¶
查找目标¶
已知姓名“小王”，查找对应工号，结果为 A003。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
A47 |
查找值“小王” |
lookup_array |
C31:C42 |
姓名列 |
return_array |
B31:B42 |
工号列，位于姓名列左侧 |
结果说明¶
函数会先在姓名列中找到“小王”，再返回左侧工号列对应的值 A003。
核心知识点或实战价值¶
XLOOKUP 最大的优势之一，就是返回范围不再受“必须位于右侧”这一限制。实际工作中遇到表结构固定、无法调整列顺序时，这一点非常有价值。
案例四、整行查找¶
使用场景¶
查找某个关键值后，一次性返回该行中的多列数据，而不是只返回单个单元格结果。
图片¶
查找目标¶
根据工号 A004，一次性返回对应的姓名和销售额。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
F33 |
查找值 A004 |
lookup_array |
B31:B42 |
工号列 |
return_array |
C31:D42 |
姓名列和销售额列，两列同时返回 |
结果说明¶
返回结果为“小吴”和 120 两个值，并自动溢出到相邻单元格中。
核心知识点或实战价值¶
这个案例体现的是 XLOOKUP 的动态数组能力。return_array 不一定只能是一列，也可以是一整块区域，因此很适合做整行信息提取。
案例五、整列查找¶
使用场景¶
当需要一次性批量查询多个姓名对应的销售额时，不必为每个姓名单独写一条公式。
图片¶
查找目标¶
批量查找小张、小李、小蒋、小苏、小马的销售额。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
F33:F37 |
一个包含 5 个姓名的查找区域 |
lookup_array |
C31:C42 |
姓名列 |
return_array |
D31:D42 |
销售额列 |
结果说明¶
一个公式即可同时返回 5 行结果，并向下自动溢出，结果为 100、130、300、160、260。
核心知识点或实战价值¶
这里的关键点在于，lookup_value 不仅可以是单个单元格，也可以是一个区域。结合 Excel 365/2021 的动态数组能力，XLOOKUP 能直接完成批量查找，大幅减少重复公式。
案例六、指定错误值¶
使用场景¶
当查找目标不存在时，不希望单元格返回 #N/A，而是显示更友好的提示信息。
图片¶
查找目标¶
查找“小111”的销售额，但数据中不存在此人。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
F32 |
查找值“小111” |
lookup_array |
C31:C42 |
姓名列 |
return_array |
D31:D42 |
销售额列 |
if_not_found |
"未找到" |
找不到时返回自定义文字 |
结果说明¶
由于“小111”不存在，最终不会显示 #N/A，而是直接返回“未找到”。
核心知识点或实战价值¶
过去这类需求常常需要在外层再套一层 IFERROR()。有了 if_not_found 参数后，错误处理可以直接内置在 XLOOKUP 里，公式更简洁，也更适合报表展示。
案例七、多条件查找¶
使用场景¶
当一个条件无法唯一确定结果时，可以把多个条件拼接起来，构造成一个组合查找键。
图片¶
查找目标¶
查找“小陈 + 健身器械”对应的销售额，结果为 140。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
A72&B72 |
将“小陈”和“健身器械”拼接为组合查找值 |
lookup_array |
F70:F84&G70:G84 |
将姓名列和产品列逐行拼接，生成虚拟组合列 |
return_array |
H70:H84 |
返回销售额列 |
结果说明¶
函数会把“姓名 + 产品”作为唯一匹配条件，在数据中找到“小陈健身器械”这一组合，并返回对应销售额 140。
核心知识点或实战价值¶
这个方法适合处理常见的多条件精确查找，而且不需要额外增加辅助列。实际使用时，要注意拼接后的组合值不要产生歧义，必要时可以增加分隔符来提高稳定性。
相关数据如下：
| 姓名 | 产品 | 销售额 |
|---|---|---|
| 小张 | 水果 | 310 |
| 小李 | 水果 | 500 |
| 小陈 | 水果 | 60 |
| 小张 | 健身器械 | 50 |
| 小陈 | 健身器械 | 140 |
| 小张 | 保健品 | 260 |
| 小陈 | 保健品 | 240 |
| … | … | … |
案例八、倒序查找¶
使用场景¶
当同一个查找值在数据中重复出现，而你要的不是第一条记录，而是最后一条记录时，就需要倒序查找。
图片¶
查找目标¶
查找最后一个“小张”对应的销售额，结果为 260。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
A77 |
查找值“小张” |
lookup_array |
F70:F84 |
姓名列，其中“小张”出现多次 |
return_array |
H70:H84 |
销售额列 |
if_not_found |
留空 | 使用默认值 |
match_mode |
留空 | 使用默认精确匹配 |
search_mode |
-1 |
从后往前进行倒序搜索 |
结果说明¶
右侧数据表里“小张”出现了 3 次，默认查找会先返回第一条 310，而 search_mode=-1 会让函数从后往前搜索，因此最终返回最后一条记录对应的 260。
核心知识点或实战价值¶
这个参数特别适合查询最后一次成交记录、最后一次打卡记录、最后一条流水信息等场景。公式里连续留空两个参数，也是很多人学习 XLOOKUP 时必须掌握的写法细节。
案例九、匹配模式¶
使用场景¶
当查找值不一定能精确命中，而是需要按区间、档位或提成标准来匹配时，就可以用匹配模式来完成近似查找。
图片¶
查找目标¶
销售额为 310 时，查找对应的提成比例，结果为 3%。
公式¶
参数解析¶
| 参数 | 实际内容 | 说明 |
|---|---|---|
lookup_value |
D90 |
查找值 310 |
lookup_array |
A88:A91 |
销售额下限列 |
return_array |
B88:B91 |
提成比例列 |
if_not_found |
留空 | 使用默认值 |
match_mode |
-1 |
精确匹配，或返回下一个更小的值 |
结果说明¶
因为 310 不在表中，所以 XLOOKUP 会根据 match_mode=-1 找到不超过 310 的最大下限值，也就是 200，再返回对应的提成比例 3%。
核心知识点或实战价值¶
这个案例非常适合提成分级、成绩分段、价格区间等业务逻辑。需要注意的是，进行这类近似匹配时，源数据通常要保持升序排列，否则结果可能不可靠。
相关区间表如下：
| 销售额下限 | 提成比例 |
|---|---|
| 50 | 1% |
| 100 | 2% |
| 200 | 3% |
| 400 | 4% |
写在最后¶
从竖向查找、横向查找，到反向查找、整行整列返回、错误值处理、多条件匹配、倒序查找和近似匹配，可以看到 XLOOKUP 已经不只是 VLOOKUP 的替代品，而是 Excel 查找类函数中更完整的一套解决方案。
如果你使用的是 Excel 365 或 Excel 2021 及以上版本，熟练掌握 XLOOKUP，基本就能覆盖大多数常见的数据查找需求。把这 9 个案例练熟之后，再回头看复杂报表或业务表格，很多原本需要多层嵌套公式的问题，往往都能用一个 XLOOKUP 更高效地解决。