EXCEL高级函数应用-XLOOKUP函数

在 Excel 数据处理中，“查找匹配” 是高频需求。过去我们依赖 VLOOKUP、HLOOKUP 或 INDEX+MATCH 组合，但这些函数要么只能向右查找，要么操作复杂。而 Excel 365/2021 版本推出的 XLOOKUP 函数，直接颠覆了传统查找逻辑 —— 既能向左查、多列查，还支持灵活匹配，堪称 “查找界的全能选手”。今天就带大家全面掌握这个宝藏函数！

---

一、先搞懂：XLOOKUP 基本语法与参数

想要灵活运用 XLOOKUP，首先得把 “基础框架” 摸透。它的语法看似复杂，实则每一个参数都对应明确的功能，记住后就能举一反三。

1. 基本语法

XLOOKUP(lookup\_value, lookup\_array, return\_array, \[if\_not\_found], \[match\_mode], \[search\_mode])

括号中带 [] 的参数为可选项，日常基础查找中，前 3 个参数是 “必选项”，后 3 个可根据需求灵活添加。

2. 参数详细说明

为了让大家更清晰理解每个参数的作用，我整理了一张 “参数说明书”，结合实际场景拆解：

参数名称	作用解释	通俗举例（比如 “查员工工资”）	是否必选
lookup_value	你要 “找什么”—— 即目标查找值，可以是文本、数字、单元格引用（如 A2）	要查找的 “员工姓名”（如 B2 单元格的 “张三”）	是
lookup_array	你要 “在哪找”—— 即搜索的区域 / 数组，必须是单行或单列	员工姓名所在列（如 “员工表” 的 A 列：A:A）	是
return_array	你要 “返回什么”—— 即匹配后要提取的结果区域 / 数组，需与 lookup_array 行数 / 列数一致	员工工资所在列（如 “员工表” 的 C 列：C:C）	是
[if_not_found]	可选：找不到匹配值时，返回的内容（默认返回 #N/A 错误）	若没找到员工，显示 “无此员工” 而非错误提示	否
[match_mode]	可选：匹配规则，共 4 种模式（默认精确匹配）	找 “近似工资范围” 用 1，找 “包含关键词” 用 2	否
[search_mode]	可选：搜索方向 / 方式，共 4 种模式（默认从左到右 / 从上到下）	找 “最后一次出现的重复值” 用 -1，排序数据用 2	否

其中，[match_mode] 和 [search_mode] 是 XLOOKUP 的 “核心优势”，支持 4 种模式，比传统函数灵活太多，后面会结合示例详细讲。

---

二、实战学：按功能拆解 XLOOKUP 使用示例

光懂参数不够，结合实际场景用起来才是关键。下面我按 “基础→进阶→高阶” 的顺序，用 8 个实用示例，带大家掌握 XLOOKUP 的核心功能。

示例 1：基础查找（替代 VLOOKUP，更简单）

需求：在 “员工表” 中，根据 A2 的 “员工姓名”，查找对应 “部门”（B 列）。

公式：

\=XLOOKUP(A2, 员工表!A:A, 员工表!B:B)

解析：

• lookup_value=A2：找 A2 单元格的员工姓名；

• lookup_array=员工表!A:A：在 “员工表” 的 A 列（姓名列）搜索；

• return_array=员工表!B:B：找到后返回对应 B 列（部门列）的内容。

💡 优势：无需像 VLOOKUP 那样指定 “返回列数”，直接选结果列即可，减少出错概率。

示例 2：带 “默认值” 查找（避免 #N/A 错误）

需求：查找 “产品表” 中 A2 产品的 “库存”，若产品不存在，显示 “未录入”。

公式：

\=XLOOKUP(A2, 产品表!A:A, 产品表!D:D, "未录入")

解析：

这里用到了可选参数 [if_not_found]，将其设为 “未录入”。当 XLOOKUP 在 “产品表 A 列” 找不到 A2 的产品时，不会返回刺眼的 #N/A，而是显示友好的文字提示，报表更美观。

示例 3：反向查找（向左查，VLOOKUP 做不到！）

需求：在 “客户表” 中，根据 B2 的 “客户编号”，查找对应 “客户姓名”（A 列，在编号列左侧）。

公式：

\=XLOOKUP(B2, 客户表!B:B, 客户表!A:A)

解析：

这是 XLOOKUP 的 “经典优势”—— 支持向左查找。传统 VLOOKUP 只能从查找列的右侧提取数据，而 XLOOKUP 完全不受方向限制，只要 lookup_array 和 return_array 对应，左列、右列都能查。

示例 4：多列同时查找（一次提取多个结果）

需求：根据 A2 的 “员工姓名”，一次性提取 “部门”（B 列）、“工资”（C 列）、“入职日期”（D 列）3 个信息。

公式：

\=XLOOKUP(A2, 员工表!A:A, 员工表!B:D)  // 选中3列单元格后输入，按Ctrl+Shift+Enter（Excel 365可直接回车）

解析：

• return_array 可以是 “多列区域”（如 B:D），而非单一列；

• 输入公式时，需先选中要显示结果的 3 个单元格（如 B2:D2），再输入公式并按组合键，即可一次性返回 3 列数据，无需重复写 3 次公式。

示例 5：近似匹配（查 “范围值”，如工资等级）

需求：根据 A2 的 “员工工资”，匹配对应的 “工资等级”（等级表中，10k 以下为 C 级，10k-20k 为 B 级，20k 以上为 A 级）。

⚠️ 前提：“等级表” 的工资范围列（A 列）需按升序排序（近似匹配要求）。

公式：

\=XLOOKUP(A2, 等级表!A:A, 等级表!B:B, , 1)

解析：

这里用到了 [match_mode]=1（近似匹配，找 “小于等于 lookup_value 的最大值”）：

• 若 A2 工资为 15k，XLOOKUP 会在 “等级表 A 列” 找到最接近 15k 且小于它的 “10k”，返回对应 B 列的 “B 级”；

• 若工资为 25k，会找到 “20k”，返回 “A 级”。

📌 场景延伸：查税率、成绩等级、快递费等 “范围类数据”，都能用这个模式。

示例 6：通配符匹配（查 “包含关键词” 的内容）

需求：在 “供应商表” 中，查找 “名称包含‘科技’” 的供应商，返回其 “联系方式”。

公式：

\=XLOOKUP("\*科技\*", 供应商表!A:A, 供应商表!C:C, "无匹配", 2)

解析：

• lookup_value="*科技*"：* 是通配符，代表 “任意多个字符”，“科技” 即 “包含‘科技’的所有文本”；

• [match_mode]=2：开启 “通配符匹配” 模式，此时 lookup_value 中的 * 和 ?（匹配单个字符）才会生效；

• 若想找 “以‘北京’开头” 的供应商，可将 lookup_value 设为 “北京 *”。

示例 7：反向搜索（找 “最后一次出现” 的重复值）

需求：在 “销售记录表” 中，A 列是 “产品名称”（有重复），查找 “最后一次销售” 的 “金额”（B 列）。

公式：

\=XLOOKUP(A2, 销售记录表!A:A, 销售记录表!B:B, , 0, -1)

解析：

这里用到了 [search_mode]=-1（从最后一行向第一行搜索）：

• 传统默认搜索（search_mode=1）会返回 “第一次出现” 的重复值，而 -1 反向搜索，能直接定位到 “最后一次” 的记录，适合查 “最新数据”。

示例 8：二分法查找（大数据提速，效率翻倍）

需求：在 “10 万行的订单表” 中，查找 A2 “订单号” 对应的 “客户”（B 列），要求快速匹配。

⚠️ 前提：“订单号列”（A 列）已按升序排序。

公式：

\=XLOOKUP(A2, 订单表!A:A, 订单表!B:B, , 0, 2)

解析：

[search_mode]=2 代表 “二分法查找”，适合 “已排序的大数据表”：

• 普通线性搜索（默认 1）会逐行扫描，10 万行数据可能卡顿；而二分法会 “减半缩小范围”，几秒内就能定位结果，效率极大提升；

• 若数据按降序排序，可将 search_mode 设为 -2。

---

三、总结：XLOOKUP 为什么比传统函数好用？

最后用一张表总结 XLOOKUP 的核心优势，帮大家快速记住它的 “过人之处”：

对比维度	XLOOKUP 函数	VLOOKUP 函数
查找方向	支持向左、向右查找	仅支持向右查找
返回列数	可返回多列，无需数 “列号”	仅返回单列，需手动输列号
错误处理	支持自定义默认值	需嵌套 IFERROR 才能处理错误
匹配模式	4 种模式（含通配符、近似）	仅 2 种模式（精确 / 近似）
大数据效率	支持二分法，速度快	仅线性搜索，效率低

掌握 XLOOKUP，能解决 80% 以上的 Excel 查找需求，让你从 “反复调整公式” 的繁琐中解放出来。赶紧打开 Excel 试试上面的示例，用一次就会爱上！