Excel/WPS交叉查询10个公式，从入门到精通！

摘要：别慌！今天我们用10个公式覆盖所有场景——从老版本通用的INDEX+MATCH，到365专属的XLOOKUP，从单结果精准提取到多结果批量输出，看完直接告别“找数据靠手戳”的低效时代！

做报表时，领导要“张三3月的销售额”？

做分析时，需要“华东区Q2的利润额”？

跨行跨列找数据，总在VLOOKUP和INDEX间反复横跳？

别慌！今天我们用10个公式覆盖所有场景——从老版本通用的INDEX+MATCH，到365专属的XLOOKUP，从单结果精准提取到多结果批量输出，看完直接告别“找数据靠手戳”的低效时代！

示例数据表

一、基础篇：老版本也能封神的6大“万能公式”

1. INDEX+MATCH：Excel界的“坐标定位器”（推荐指数：★★★★★）

公式：=INDEX(C3:H7, MATCH(B10, B3:B7, 0), MATCH(C10, C2:H2, 0))

一句话拆解：

MATCH(B10, B3:B7, 0)：在姓名列（B3:B7）找到“张三”的行号（比如第2行）；MATCH(C10, C2:H2, 0)：在月份行（C2:H2）找到“3月”的列号（比如第3列）；INDEX(C3:H7, 行号, 列号)：直接定位到“第2行第3列”的数值（即张三3月销售额）。

✨ 优势：不挑Excel版本，支持任意方向查找，数据量大时速度超快！

2. VLOOKUP+MATCH：新手友好的“懒人神器”（推荐指数：★★★★☆）

公式：=VLOOKUP(B10, $B$3:$H$7, MATCH(C10, $C$2:$H$2, 0)+1, 0)

关键细节：

VLOOKUP只能在首列找值，所以需要用MATCH动态计算“目标列在数据区域中的位置”；+1是因为数据区域（B3:H7）的首列是姓名列（B列），而MATCH返回的是月份行在C2:H2中的列号（比如3月是第2列），所以要+1才能对齐。

⚠️ 注意：如果数据区域首列不是查找列（比如用月份找姓名），这个公式会失效！

3. SUMPRODUCT：非数组公式的“低调王者”（推荐指数：★★★★☆）

公式：=SUMPRODUCT(($B$3:$B$7=B10)*($C$2:$H$2=C10)*$C$3:$H$7)

原理揭秘：

(B3:B7=B10)：生成一个由TRUE/FALSE组成的数组（TRUE=1，FALSE=0），标记所有“张三”的行；(C2:H2=C10)：同理，标记所有“3月”的列；两个数组相乘后，只有“张三+3月”的交叉点会是1×1=1，其余都是0；最后乘以数据区域（C3:H7），直接提取目标值。

✨ 亮点：无需数组公式（不用按Ctrl+Shift+Enter），新手友好！

4. OFFSET+MATCH：“偏移量”定位法（推荐指数：★★★☆☆）

公式：=OFFSET($B$2, MATCH(B10, B3:B7, 0), MATCH(C10, C2:H2, 0))

适用场景：需要动态扩展数据区域时（比如按月新增列）。

B2是基准点（表格左上角）；MATCH(B10, B3:B7, 0)计算行偏移量（比如“张三”在第2行，偏移量是2-1=1）；MATCH(C10, C2:H2, 0)计算列偏移量（比如“3月”在第3列，偏移量是3-1=2）；OFFSET从基准点向下偏移1行、向右偏移2列，正好是目标值。

⚠️ 缺点：依赖绝对引用，数据区域变动时需手动调整基准点。

5. HLOOKUP+MATCH：“水平版VLOOKUP”（推荐指数：★★★☆☆）

公式：=HLOOKUP(C10, C2:H7, MATCH(B10, B2:B7, 0), 0)

适合场景：表格“行少列多”（比如10行20列的销售明细）。

HLOOKUP是“水平查找”，在首行（C2:H2）找“3月”的列号；MATCH(B10, B2:B7, 0)确定“张三”在数据区域（C2:H7）中的行号（比如第2行）；最终返回“第2行”的数值。

✨ 对比VLOOKUP：不用嵌套MATCH找列号，更直观！

6. SUM/ MAX数组公式：“极简但挑场景”（推荐指数：★★☆☆☆）

公式：

SUM版（需三键）：=SUM((B3:B7=B10)*(C2:H2=C10)*(C3:H7))MAX版（需三键）：=MAX((B3:B7=B10)*(C2:H2=C10)*(C3:H7))

适用场景：数据无重复（比如每个“姓名+月份”组合只有一个值）。

SUM版：通过布尔逻辑（TRUE=1，FALSE=0）相乘，只保留目标值并求和（其实就是提取唯一值）；MAX版：同理，但如果有多个匹配值会返回最大值（慎用！）。

⚠️ 缺点：必须按Ctrl+Shift+Enter输入，新版本用户可能觉得麻烦。

二、进阶篇：365/WPS专属的5大“效率核弹”

7. XLOOKUP双嵌套：“查找界的六边形战士”（推荐指数：★★★★★）

公式：=XLOOKUP(B10, B$3:B$7, XLOOKUP(C10, C$2:H$2, C$3:H$7))

拆解：

内层XLOOKUP(C10, C2:H2, C3:H7)：先在月份行找“3月”，返回对应的整列数据（比如3月列的所有姓名销售额）；外层XLOOKUP(B10, B3:B7, 上述结果)：再在姓名列找“张三”，直接提取目标值。

✨ 优势：

不用记列号，自动匹配；支持反向查找（比如用销售额找姓名）；找不到值时可自定义提示（比如=XLOOKUP(..., "未找到")）。

8. FILTER双嵌套：“多结果批量提取王”（推荐指数：★★★★☆）

公式：=FILTER(FILTER(C$3:H$7, C$2:H$2=C10), B$3:B$7=B10)

玩法：

内层FILTER(C3:H7, C2:H2=C10)：筛选出“3月”对应的整列数据（所有姓名的3月销售额）；外层FILTER(上述结果, B3:B7=B10)：从内层结果中再筛选出“张三”的值。

✨ 高能操作：如果有多个“张三+3月”的记录（比如不同产品线），会直接返回所有结果（数组形式）。

9. TOCOL+IF：“文本/数值通吃”（推荐指数：★★★☆☆）

公式：=TOCOL(IF((C$2:H$2=C10)*(B$3:B$7=B10), C$3:H$7, x), 2)

解析：

IF((条件1)*(条件2), 数据, x)：当“姓名=张三”且“月份=3月”时，返回对应数值，否则返回错误值x；TOCOL(..., 2)：忽略所有错误值，只保留有效数值（适合提取单个或多个结果）。

✨ 适用：需要将结果转为文本格式，或与其他函数配合使用（比如TEXTJOIN）。

10. CONCAT/TEXTJOIN：“文本结果专用”（推荐指数：★★★☆☆）

公式：

CONCAT版：=CONCAT(IF((C$2:H$2=C10)*(B$3:B$7=B10), C$3:H$7, ""))TEXTJOIN版：=TEXTJOIN(", ", , IF((C$2:H$2=C10)*(B$3:B$7=B10), C$3:H$7, ""))

区别：

CONCAT直接拼接结果（比如多个数值会连在一起“100200”）；TEXTJOIN可加分隔符（比如, 分隔后是“100, 200”），更易读。

✨ 适用：查找结果是文本（比如姓名、产品编码），或多结果需要批量展示。

三、公式对比表：一键选对“最优解”

公式版本要求推荐指数核心优势适合场景INDEX+MATCH所有版本★★★★★稳定、通用、速度快通用双向查找XLOOKUP双嵌套365/WPS最新版★★★★★灵活、反向查找、自定义提示新版本用户/复杂查找VLOOKUP+MATCH所有版本★★★★☆新手友好、无需记忆复杂函数初学者/简单双向查找FILTER双嵌套365/WPS最新版★★★★☆多结果提取、动态数组需要批量输出的场景SUMPRODUCT所有版本★★★★☆非数组公式、无需三键小数据量/无重复值场景

四、避坑指南：90%人踩过的4个雷区

1. 错误处理：用IFERROR“兜底”

所有公式都可以套上=IFERROR(你的公式, "未找到")，避免#N/A报错影响报表美观。

2. 性能优化：拒绝“整列引用”

比如把B:B改成B3:B7，减少Excel的计算量（大数据量时速度能提升10倍！）。

3. 版本适配：别用新函数“坑”老用户

如果同事用的是Excel 2016，别硬推XLOOKUP，改用INDEX+MATCH更稳妥。

4. 数据唯一性：多结果要谨慎

如果“姓名+月份”可能重复（比如同一人不同产品），优先用FILTER或TEXTJOIN展示所有结果，避免漏数据。

五、常见问题答疑

Q：为什么我的VLOOKUP+MATCH总报错？

A：检查两点：① MATCH的第二个参数是否是查找值所在的行（比如月份行）；② VLOOKUP的第三个参数是否是MATCH结果+1（因为VLOOKUP从数据区域首列开始计数）。