第 6 章：高级函数与数组公式¶

从 VLOOKUP 到 XLOOKUP——掌握 Excel 的查找引用引擎

查找与引用函数是 Excel 中最常用的高级函数。本章从经典的 VLOOKUP 出发，介绍更强大的 XLOOKUP 和 INDEX-MATCH 组合，以及动态数组函数。

6.1 VLOOKUP：经典但有限¶

=VLOOKUP(查找值, 查找范围, 返回列号, 匹配模式)

VLOOKUP 的陷阱

=VLOOKUP(A1,B:C,2) —— 第四个参数省略，默认近似匹配，可能返回错误结果。 永远写全四个参数：=VLOOKUP(A1,B:C,2,FALSE)

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

实战：XLOOKUP 替代 VLOOKUP

VLOOKUP: =VLOOKUP(A2, B:D, 3, FALSE)
XLOOKUP: =XLOOKUP(A2, B:B, D:D, "未找到")

XLOOKUP 的优势： - 查找范围和返回范围分离， 不要求查找值在第一列 - 可以向左查找 - 自带错误处理（第四个参数） - 插入/删除列不影响公式

XLOOKUP 的可用性

XLOOKUP 在 Excel 2021 和 Microsoft 365 中可用。如果你使用的是 Excel 2019 或更早版本，请使用 INDEX-MATCH 替代。

INDEX-MATCH 是 VLOOKUP 出现之前的"万能查找方案"，至今仍是兼容性最好的方案。

=INDEX(返回范围, MATCH(查找值, 查找范围, 0))

函数	作用	比喻
`MATCH`	找到查找值在第几行	GPS 定位——找到坐标
`INDEX`	返回该行指定列的值	取货——根据坐标拿东西

实战：INDEX-MATCH 双向查找

=INDEX(B2:E10, MATCH(G2, A2:A10, 0), MATCH(H2, B1:E1, 0))

这实现了"根据姓名和月份查找销售额"——先 MATCH 找到行号，再 MATCH 找到列号，最后 INDEX 取值。

对比维度	VLOOKUP	INDEX-MATCH	XLOOKUP
向左查找	❌	✅	✅
列插入安全	❌	✅	✅
多条件查找	需要辅助列	✅	✅
语法简洁	⭐⭐⭐	⭐	⭐⭐⭐
兼容性	所有版本	所有版本	2021+ / 365
性能	中等	较快	较快

传统公式返回单个值， 动态数组公式 可以自动"溢出"到相邻单元格，返回多个结果。

实战：动态筛选 Top 5

=SORT(FILTER(A2:C100, C2:C100>10000), 3, -1)

这个公式：先筛选销售额 > 10000 的记录，再按销售额降序排列。结果自动溢出到相邻单元格。

引用动态数组的整个溢出范围，使用 # 后缀：

=A2#    引用 A2 单元格溢出的整个数组

下一章预告： 第 7 章：宏与 VBA 入门 —— 录制你的第一个宏，用 VBA 自动化重复工作。