夜雨聆风
办公软件Excel 查找与引用全攻略四(VLOOKUP、INDEX+MATCH、OFFSET…)
从“照镜子”到“遥控器”：让Excel帮你自动找数据
一、VLOOKUP —— 垂直查找之王
语法=VLOOKUP(查找值, 表格区域, 返回列号, [匹配方式])
详细使用分析
匹配方式：
-
0或FALSE：精确匹配（常用） -
1或TRUE：近似匹配（区间查找）
三步走理解
① 在第一列找“查找值”
② 找到后，向右数“返回列号”列
③ 把那个单元格的值取回来
经典案例=VLOOKUP(E2, A:C, 3, 0)
→ 在 A 列找 E2 的值，返回同一行 C 列（第3列）的内容
⚠️ 四大常见坑点
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
-- 或 &"" 统一格式 |
|
|
|
$A$2:$C$100 锁定 |
|
|
|
0 或 FALSE |
近似匹配用法（税率计算）=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("B2") |
|
|
|
INDIRECT("R2C2", FALSE) |
|
王牌用法：间接引用工作表
=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 列
速查总结表（建议收藏）
|
|
|
|
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
