Excel任意方向查找、反向查找、多条件查找、自定义默认值--XLOOKUP函数

XLOOKUP 是 Excel 365/2021 及后续版本新增的新一代动态查找函数，专为解决传统查找函数（VLOOKUP/HLOOKUP/INDEX+MATCH）的痛点设计，支持任意方向查找、反向查找、多条件查找、自定义默认值等功能，且语法更简洁、逻辑更直观。

一、函数核心语法与参数说明

1. 完整语法

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

2. 参数逐义解析

参数名	必选/可选	数据类型	核心作用	注意事项
lookup_value	必选	任意类型（文本/数字/单元格引用）	查找目标：要在 lookup_array 中寻找的值	可直接输入常量（如 "张三"/1001），或引用单元格
lookup_array	必选	单行/单列区域/一维数组	查找区域：存放查找目标的区域，仅支持单行或单列	区域大小必须与 return_array 完全一致（行数/列数相同）
return_array	必选	单行/单列区域/一维数组	返回区域：找到匹配值后，要返回的结果区域，仅支持单行或单列	与 lookup_array 一一对应，匹配到第 N 个值，就返回 return_array 第 N 个值
[if_not_found]	可选	任意类型	未找到默认值：当 lookup_array 中无匹配项时，返回的自定义结果	省略时默认返回 #N/A 错误
[match_mode]	可选	数字（0/1/-1/2）	匹配模式：定义查找的匹配规则	0 = 精确匹配（默认）；1 = 近似匹配（升序）；-1 = 近似匹配（降序）；2 = 通配符匹配
[search_mode]	可选	数字（1/-1/2/-2）	搜索模式：定义查找的搜索方向和规则	1 = 从前往后搜索（默认）；-1 = 从后往前搜索；2 = 二分法（升序）；-2 = 二分法（降序）

3. 关键特性

• 方向自由：无需区分垂直/水平查找，lookup_array 和 return_array 可以是任意单行/单列。
• 反向查找：无需调整数据源顺序，直接实现“由值找键”（如根据姓名找工号）。
• 动态适配：支持与动态数组函数（FILTER/SORT）嵌套，返回多结果溢出。
• 错误友好：通过 if_not_found 自定义未找到的提示，避免 #N/A 错误。

二、数据源准备（通用示例数据）

为统一示例，使用员工信息表作为基础数据源，共 5 行数据：

单元格区域	A (员工ID)	B (姓名)	C (部门)	D (月薪)
1	1001	张三	销售部	8000
2	1002	李四	技术部	12000
3	1003	王五	销售部	9000
4	1004	赵六	技术部	15000
5	1005	钱七	市场部	7500

后续所有示例均基于此表，数据区域记为 A1:D5。

三、初级用法：基础单条件精确查找

场景1：正向查找（替代 VLOOKUP）

需求：根据员工ID（1003），查找对应的姓名。

公式编写

=XLOOKUP(1003, A2:A5, B2:B5)

参数对应说明

参数	取值	作用
lookup_value	1003	查找目标：要找的员工ID
lookup_array	A2:A5	查找区域：存放员工ID的列（A列）
return_array	B2:B5	返回区域：存放姓名的列（B列），与A列一一对应
[if_not_found]	省略	未找到时返回 #N/A
[match_mode]	省略（默认0）	精确匹配，必须找到完全等于1003的值才返回结果
[search_mode]	省略（默认1）	从前往后搜索，找到第一个匹配值即停止

结果

王五

场景2：自定义未找到提示

需求：查找员工ID=1006的姓名，若不存在则返回“无此员工”。

公式编写

=XLOOKUP(1006, A2:A5, B2:B5, "无此员工")

关键参数：if_not_found="无此员工"

当 lookup_array 中无 1006 时，不再返回 #N/A，而是返回自定义文本。

结果

无此员工

场景3：水平查找（替代 HLOOKUP）

需求：在水平数据源中，根据表头（月薪）查找张三的月薪。

水平数据源（G1:K2）

G1	H1	I1	J1	K1
姓名	张三	李四	王五	赵六
月薪	8000	12000	9000	15000

公式编写

=XLOOKUP("张三", H1:K1, H2:K2)

参数说明

• lookup_array 和 return_array 为水平单行区域，XLOOKUP 自动适配水平查找，无需额外设置。

结果

8000

四、中级用法：反向查找与模糊匹配

场景1：反向查找（传统方法需 INDEX+MATCH）

需求：根据姓名（李四），查找对应的员工ID（传统 VLOOKUP 无法直接实现，因为姓名不在首列）。

公式编写

=XLOOKUP("李四", B2:B5, A2:A5)

核心优势

lookup_array 可以是任意列（此处为姓名列 B 列），return_array 可以是左侧的 ID 列 A 列，无需调整数据源顺序，直接实现反向查找。

结果

1002

场景2：通配符模糊匹配（match_mode=2）

需求：查找**姓“张”**的员工的月薪（姓名以“张”开头）。

公式编写

=XLOOKUP("张*", B2:B5, D2:D5, "无匹配", 2)

关键参数解析

参数	取值	作用
lookup_value	"张*"	通配符规则：* 代表任意多个字符，张* 匹配所有以“张”开头的姓名
[match_mode]	2	启用通配符匹配模式，支持 *（任意字符）、?（单个字符）、~（转义）

结果

8000

场景3：近似匹配（区间查找）

需求：根据分数（85），查找对应的等级（等级规则：≥90=优秀，≥80=良好，≥70=中等，<70=及格）。

等级对照表（F1:G5）

F1 (分数下限)	G1 (等级)
90	优秀
80	良好
70	中等
60	及格

公式编写

=XLOOKUP(85, F2:F5, G2:G5, "无等级", 1)

关键参数：match_mode=1

• 近似匹配（升序）：当无精确匹配值时，返回小于且最接近查找值的结果。
• 要求 lookup_array 必须按升序排列。
• 85 无精确匹配，小于85且最接近的是80，对应等级“良好”。

结果

良好

场景4：从后往前搜索（search_mode=-1）

需求：查找销售部最后一位员工的姓名（数据源中有2个销售部员工：张三、王五）。

公式编写

=XLOOKUP("销售部", C2:C5, B2:B5, "无", 0, -1)

关键参数：search_mode=-1

• 从后往前搜索，找到最后一个匹配值即停止，而非第一个。
• 销售部对应的姓名是张三（第1个）、王五（第2个），从后往前搜会返回王五。

结果

王五

五、高级用法：多条件查找与函数嵌套

场景1：多条件查找（用 & 连接条件）

需求：查找**部门=技术部 且 月薪=15000**的员工姓名。

公式编写

=XLOOKUP(1, (C2:C5="技术部")*(D2:D5=15000), B2:B5, "无匹配")

核心逻辑解析

1. 构造条件数组：
   • (C2:C5="技术部") 生成布尔数组 {FALSE, TRUE, FALSE, TRUE, FALSE}
   • (D2:D5=15000) 生成布尔数组 {FALSE, FALSE, FALSE, TRUE, FALSE}
   • 两个数组相乘 *，布尔值自动转为 0/1，结果为 {0, 0, 0, 1, 0}
2. 查找目标设为1：lookup_value=1，即找到条件同时满足的位置（值为1的元素）。
3. 返回对应姓名：值为1的位置对应姓名“赵六”。

结果

赵六

场景2：动态数组溢出（返回多个结果）

需求：查找所有销售部员工的姓名（返回多个结果，自动溢出到单元格）。

公式编写

=XLOOKUP("销售部", C2:C5, B2:B5, "", 2, 1)

关键特性：动态溢出

当 lookup_array 中有多个匹配值时，XLOOKUP 会返回所有匹配结果，自动向下溢出到相邻单元格（需 Excel 365 动态数组支持）。

溢出结果

单元格	内容
E2	张三
E3	王五

场景3：嵌套 SORT 排序查找结果

需求：查找所有技术部员工的月薪，并按降序排序。

公式编写

=SORT(XLOOKUP("技术部", C2:C5, D2:D5, "", 2, 1), 1, -1)

嵌套逻辑

1. 先用 XLOOKUP 提取技术部所有月薪，返回数组 {12000, 15000}；
2. 再用 SORT 函数对数组按第1列（唯一列）降序（-1）排序；
3. 最终结果自动溢出。

溢出结果

单元格	内容
E2	15000
E3	12000

场景4：与 FILTER 嵌套实现更灵活筛选

需求：查找月薪＞8000的员工姓名和部门，返回两列结果。

公式编写

=FILTER(HSTACK(B2:B5, C2:C5), D2:D5>8000)

补充：XLOOKUP 单条件返回单列，多列结果建议用 FILTER+HSTACK 嵌套，更高效。

结果（溢出两列）

姓名	部门
李四	技术部
王五	销售部
赵六	技术部

六、常见问题与注意事项

1. #N/A 错误的解决

   • 原因：lookup_array 中无匹配值且未设置 if_not_found；
   • 解决：添加 if_not_found 参数（如 if_not_found="无数据"）。

2. #SPILL! 错误的解决

   • 原因：溢出结果区域被其他单元格内容阻挡；
   • 解决：清空目标区域下方/右侧的空白单元格，确保溢出空间充足。

3. lookup_array 与 return_array 长度不一致

   • 报错：#VALUE!；
   • 解决：确保两个区域的行数/列数完全相同（如均为 4 行 1 列）。

4. 近似匹配的排序要求

   • match_mode=1（升序近似）：lookup_array 必须升序排列；
   • match_mode=-1（降序近似）：lookup_array 必须降序排列；
   • 否则会返回错误结果。

5. 通配符的转义使用

   • 若需查找实际的 */?，需在前面加转义符 ~，例如查找 A* 需写 A~*。

七、XLOOKUP 对比传统查找函数的优势

功能特性	XLOOKUP	VLOOKUP	HLOOKUP	INDEX+MATCH
查找方向	任意方向（行/列）	仅垂直（列）	仅水平（行）	任意方向
反向查找	直接支持	不支持（需调整数据源）	不支持	支持（公式复杂）
多条件查找	支持（数组相乘）	不支持	不支持	支持（公式复杂）
未找到默认值	内置参数	需嵌套 IFERROR	需嵌套 IFERROR	需嵌套 IFERROR
搜索方向	支持从后往前	仅从前往后	仅从前往后	支持（需复杂设置）
通配符匹配	内置参数	支持（需手动设置）	支持（需手动设置）	支持（需手动设置）