办公软件Excel 查找与引用全攻略四(VLOOKUP、INDEX+MATCH、OFFSET…)

从“照镜子”到“遥控器”：让Excel帮你自动找数据

一、VLOOKUP —— 垂直查找之王

语法
=VLOOKUP(查找值, 表格区域, 返回列号, [匹配方式])

详细使用分析

匹配方式：

• 0 或 FALSE：精确匹配（常用）

• 1 或 TRUE：近似匹配（区间查找）

三步走理解
① 在第一列找“查找值”
② 找到后，向右数“返回列号”列
③ 把那个单元格的值取回来

经典案例
=VLOOKUP(E2, A:C, 3, 0)
→ 在 A 列找 E2 的值，返回同一行 C 列（第3列）的内容

⚠️ 四大常见坑点

近似匹配用法（税率计算）
=VLOOKUP(应纳税额, 税率表, 2, 1) — 区间查找，自动匹配到小于等于的值

二、HLOOKUP —— 水平查找

语法
=HLOOKUP(查找值, 表格区域, 返回行号, [匹配方式])

详细使用分析

与 VLOOKUP 对称：

• VLOOKUP：纵向查找（查列）

• HLOOKUP：横向查找（查行）

使用场景
表头在第一行，数据向右展开的横向结构

示例
=HLOOKUP(B1, A1:D4, 3, 0)
→ 在第1行找 B1 的值，找到后向下数3行，取该单元格

⚠️ 注意
大多数表格是纵向结构，HLOOKUP 使用频率远低于 VLOOKUP。

三、LOOKUP —— 老牌向量查找

语法（向量形式）
=LOOKUP(查找值, 查找向量, 返回向量)

详细使用分析

特点

• 默认近似匹配（升序排列）

• 可以处理数组和向量

• 比 VLOOKUP 更灵活，但要求查找向量升序

经典用法：区间查找（无需辅助列）

=LOOKUP(A1, {0,60,80,90}, {"不及格","及格","良好","优秀"})

向量 vs 数组形式

• 向量形式：LOOKUP(值, 单列, 单列) ✅ 推荐

• 数组形式：LOOKUP(值, 二维区域) ⚠️ 容易混乱，少用

⚠️ 注意
查找向量必须升序排列，否则结果不可预测。

四、MATCH —— 返回位置

语法
=MATCH(查找值, 查找区域, [匹配方式])

详细使用分析

匹配方式：

• 0：精确匹配（最常用）

• 1：小于等于（升序）

• -1：大于等于（降序）

返回值是数字（位置，不是内容）

示例
=MATCH("张三", A:A, 0) → 返回张三在第几行

核心价值
MATCH 不直接取数，而是告诉 INDEX“该往哪走”。

五、INDEX —— 根据坐标取数

语法（最常用形式）
=INDEX(区域, 行号, [列号])

详细使用分析

INDEX 是一个定位器：
你告诉它“在哪个区域、第几行、第几列”，它把那个值拿出来。

示例
=INDEX(A1:C10, 3, 2) → A1:C10 区域中，第3行第2列的值

配合 MATCH 使用（黄金组合）
=INDEX(返回列, MATCH(查找值, 查找列, 0))

实战案例
=INDEX(C:C, MATCH(E2, A:A, 0))
→ 在 A 列找到 E2 的位置，返回同一行 C 列的值

✅ INDEX+MATCH 优于 VLOOKUP 的地方

• 查找列不需要在第一列

• 向左查找也支持

• 插入/删除列不影响公式

• 计算效率更高（大数据量明显）

六、CHOOSE —— 按索引选值

语法
=CHOOSE(索引号, 值1, [值2], …)

详细使用分析

索引号 = 1 → 返回值1
索引号 = 2 → 返回值2
最多 254 个值

典型应用

① 根据数字返回文本
=CHOOSE(A1, "一", "二", "三")

② 动态切换列
=CHOOSE(月份, 一月表!B2, 二月表!B2, …)

③ 反向 VLOOKUP（组合 INDEX+CHOOSE）
=VLOOKUP(E2, CHOOSE({1,2}, B:B, A:A), 2, 0)
→ 把 B 列当第一列查找，返回 A 列

七、OFFSET —— 偏移引用

语法
=OFFSET(起点, 偏移行数, 偏移列数, [高度], [宽度])

详细使用分析

本质：从起点出发，向下/右走 N 行/列，取一片区域

参数解读

经典应用

① 动态获取最后一行数据
=OFFSET(A1, COUNTA(A:A)-1, 0)

② 滚动最近7天求和
=SUM(OFFSET(A1, COUNTA(A:A)-7, 0, 7, 1))

③ 制作动态下拉菜单（名称管理器中使用）

⚠️ 注意
OFFSET 是易失函数（任何操作都会重算），大数据慎用，优先考虑 INDEX。

八、INDIRECT —— 文本转引用

语法
=INDIRECT(文本形式的引用, [引用样式])

详细使用分析

核心能力：把字符串变成真正的单元格引用

两种引用样式

王牌用法：间接引用工作表

=INDIRECT("'"&A1&"'!B2")
→ A1 单元格写“1月”，就去取 1月!B2 的值

动态汇总多张表
=SUM(INDIRECT(B1&"!C:C")) — B1 写表名，自动切换汇总

⚠️ 注意

• 也是易失函数

• 被引用的单元格变动，不会触发 INDIRECT 自动更新

• 尽量少在大型模型中使用

九、ROW / COLUMN —— 行号与列号

语法
=ROW([单元格])
=COLUMN([单元格])

详细使用分析

不填参数 → 返回公式所在单元格的行号/列号
填参数 → 返回指定单元格的行号/列号

高级应用

① 自动序号（删除行后不断号）
=ROW()-1

② 每隔 N 行取值
=INDEX(A:A, ROW(A1)*5)

③ 动态构建序列
=ROW(1:10) → 生成 {1;2;3;4;5;6;7;8;9;10}

④ COLUMN 做横向序号
=COLUMN(A1) 向右拉 → 1,2,3,4…

⑤ 单行转多行多列
=OFFSET($A$1, (ROW(A1)-1)*3+COLUMN(A1)-1, 0)

综合实战：一套完整的查找体系

场景：多表员工信息查询

• 表名放在 B1 单元格

• 工号在 A2 单元格

• 要查该员工的姓名

公式
=INDIRECT("'"&B1&"'!C"&MATCH(A2, INDIRECT("'"&B1&"'!A:A"), 0))

逐层拆解
① INDIRECT("'"&B1&"'!A:A") → 动态引用表名的 A 列
② MATCH(A2, … , 0) → 找到工号所在行号
③ INDIRECT("'"&B1&"'!C"&行号) → 动态取该行 C 列

速查总结表（建议收藏）