一、为什么你需要XLOOKUP？

在Excel中，查找数据是日常工作中最频繁的操作之一。过去，我们依赖VLOOKUP或HLOOKUP，但它们有诸多限制：

• VLOOKUP：查找列必须在数据表的第一列，且只能向右查找。
• HLOOKUP：查找行必须在数据表的第一行，且只能向下查找。
• 两者都不支持反向查找，且处理错误值（如#N/A）时不够灵活。

XLOOKUP（在Excel 2021及Office 365中引入）彻底解决了这些问题，被誉为“查找函数的终极形态”。

二、XLOOKUP的基本语法

=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时的返回值], [匹配模式], [搜索模式])

-|-|-
参数|说明|是否必填
查找值|你要查找的内容|是
查找数组|在哪一列/行中查找|是
返回数组|返回哪一列/行的结果|是
未找到时的返回值|可选，匹配失败时返回自定义文本|否
匹配模式|0=精确匹配（默认），-1=小于，1=大于，2=通配符|否
搜索模式|1=从第一个开始搜索（默认），-1=从最后一个搜索，2=二分查找|否

三、XLOOKUP vs VLOOKUP：核心优势对比

-|-|-
功能|VLOOKUP|XLOOKUP
查找方向|只能向右|任意方向（左、右、上、下）
查找列位置|必须位于第一列|任意位置
返回列指定|整数（第几列）|直接指定返回数组
错误处理|需嵌套IFERROR|内置“未找到”参数
多条件查找|需辅助列|可直接用数组公式
通配符支持|支持|支持（匹配模式=2）

四、XLOOKUP的常见应用场景

场景1：经典单条件查找（替代VLOOKUP）
问题：根据员工ID查找姓名。

VLOOKUP写法：

=VLOOKUP(A2, A:D, 2, 0)

XLOOKUP写法：

=XLOOKUP(A2, A:A, B:B)

优势：无需计算列序号，直接指定返回列。

场景2：反向查找（VLOOKUP做不到）
问题：根据员工姓名查找员工ID（姓名在ID右侧）。

VLOOKUP：需要借助INDEX+MATCH组合。

XLOOKUP写法：

=XLOOKUP(B2, B:B, A:A)

优势：直接向左查找，公式更简洁。

场景3：多条件查找
问题：根据“部门”和“职位”两个条件查找“薪资”。

=XLOOKUP(1, (A:A=E2)*(B:B=F2), C:C)

说明：(A:A=E2)*(B:B=F2) 会生成一个由0和1组成的数组，只有两个条件同时满足时为1，XLOOKUP精确匹配1，返回对应的薪资。

场景4：处理查找失败的情况
问题：查找某个不存在的ID时，返回自定义提示而非错误值。

=XLOOKUP(A2, A:A, B:B, "未找到此人")

优势：无需嵌套IFERROR，公式更清爽。

场景5：从右向左查找最后一个匹配项
问题：查找某员工最后一次加班记录。

=XLOOKUP(A2, A:A, B:B, "无记录", 0, -1)

说明：第六个参数 -1 表示从数组的最后一个值开始搜索，返回最后一个匹配项，常用于“最后一次”场景。

五、XLOOKUP的局限与注意事项

-|-
局限|说明
两个数组|公式中两个数组需要保持相同大小，例如：A:A对应B:B即A列对B列；A1:A10,B11:B20,不需要都是1开始，但必需保持数组中元素个数相同。
wps office|ET 11.2及以上版本
ms office|Excel 2021及以上版本、Office 365支持
性能问题|在大数据量（>10万行）中，速度可能慢于INDEX+MATCH
精确匹配默认|不指定匹配模式时，默认为0（精确匹配），注意区分
数组公式依赖|多条件查找依赖动态数组，旧版本可能不支持

六、总结：什么时候用XLOOKUP？

-|-
场景|推荐函数
需要反向查找（向左查找）|XLOOKUP
需要多条件查找|XLOOKUP 或 INDEX+MATCH
需要处理#N/A错误|XLOOKUP
需要从右到左查找最后一项|XLOOKUP
数据量极大（>10万行）|INDEX+MATCH（性能更优）
老版本Excel（2019及以前）|INDEX+MATCH

七、一句话记忆

XLOOKUP = VLOOKUP的升级版 + HLOOKUP + INDEX+MATCH的简化版，且支持反向查找、错误处理和灵活搜索方向。