新版 Excel 替代 INDEX+MATCH!双层 XLOOKUP 行列双向查找教程

业务场景

二维业绩矩阵表：

A 列为姓名（纵向行标识），第 1 行为季度（横向列标识），中间区域存放销量。

需求：给定【姓名】、【季度】两个条件，自动取出二者交叉位置对应的销量。

示例：姓名 = 王五，季度 = 3 季度，查询结果：300。

完整公式（I2 单元格，推荐引用单元格版本）

=XLOOKUP(G2,A2:A6,XLOOKUP(H2,B1:E1,B2:E6,""),"")

XLOOKUP 基础语法回顾

=XLOOKUP(查找值, 查找区域, 返回区域, [找不到兜底文本])

内层 XLOOKUP 先完成横向列查找，外层 XLOOKUP 执行纵向行查找，嵌套实现双向定位。

公式由内向外逐层拆解

=XLOOKUP(G2,A2:A6,XLOOKUP(H2,B1:E1,B2:E6,""),"")

第 1 层：内层 XLOOKUP 横向查找（先锁定目标列）

XLOOKUP(H2,B1:E1,B2:E6,"")

查找值：H2【3 季度】

查找区域：B1:E1（季度表头行）

返回区域：B2:E6（整张销量数据区域）

作用：找到 “3 季度” 所在列，返回整列销量数组：{150;400;300;500;550}

第 2 层：外层 XLOOKUP 纵向查找（再锁定目标行）

XLOOKUP(G2,A2:A6, 内层结果,"")

查找值：G2【王五】

查找区域：A2:A6（姓名列）

返回区域：内层拿到的「3 季度整列销量数组」

作用：在姓名列找到王五所在第 3 行，提取数组中第 3 个数值，最终得到300。

完整演算流程：

内层找到【3 季度】→ 获取 3 季度全部人员销量 {150;400;300;500;550}

外层在姓名列定位【王五】，取出对应位置数字 → 300

可复制原始数据表

姓名	1 季度	2 季度	3 季度	4 季度
张三	100	200	150	516
李四	300	250	400	217
王五	500	350	300	383
赵六	200	450	500	470
孙七	400	600	550	423

查询条件与输出结果

姓名 (G2)	季度 (H2)	销量 (I2 输出)
王五	3 季度	300

拓展通用模板

模板 1：标准二维交叉查询（引用单元格，下拉通用）

=XLOOKUP(行条件,行标签列,XLOOKUP(列条件,列表头行,数值区域,""),"")

模板 2：文字直接写进公式（图中示例写法，不推荐下拉）

=XLOOKUP("王五",A2:A6,XLOOKUP("3季度",B1:E1,B2:E6,""),"")

模板 3：调整兜底提示文字

=XLOOKUP(G2,A2:A6,XLOOKUP(H2,B1:E1,B2:E6,"暂无该季度"),"暂无该员工")

横向对比：XLOOKUP 嵌套 VS INDEX + 双 MATCH

1. INDEX + 双 MATCH（全 Excel 版本通用）

=INDEX(B2:E6,MATCH(G2,A2:A6,0),MATCH(H2,B1:E1,0))

✅ 优势：兼容 2016/2019/365 所有版本；

❌ 找不到数据默认返回#N/A，需要额外套 IFERROR。

2. XLOOKUP 双层嵌套（仅 Excel365 可用）

✅ 优势：自带兜底参数，不用嵌套 IFERROR；阅读逻辑更直观；支持反向查找、模糊匹配拓展；

❌ 限制：低版本 Excel 无法运行。

选型建议：办公软件为新版 365，优先使用双层 XLOOKUP；需要发给旧版本电脑打开，使用 INDEX + 双 MATCH 方案。

职场适用场景

业绩矩阵：人员 + 季度、门店 + 月份双向查询销售额；

成绩表：学生姓名 + 科目提取分数；

报价表：规格 + 材质双向查找单价；

库存台账：物料 + 仓库查询库存数量。

避坑关键提醒

版本限制：XLOOKUP 仅 Excel 365 / Excel 网页版支持，2019 及更早版本不可用；

区域范围对齐：内层返回区域B2:E6，行列数量必须和表头、姓名列匹配；

顺序不能颠倒：内层横向查找列，外层纵向查找行，顺序颠倒取值出错；

文本不要携带多余空格，空格会造成匹配失败；

自上而下优先匹配：存在重复姓名、重复表头时，只会返回第一条匹配数据。

相关学习资料