📌 阶段三 · 函数核心 · 第12讲 MATCH+INDEX函数 · 约 45 分钟

补上 VLOOKUP 的短板：按商品名称反查编号，行×列双向定位，搭一个给同事用的查询台

学习目标

  • 理解 MATCH 找位置、INDEX 取值的分工，会单独使用两者
  • 用 INDEX+MATCH 实现从右向左的反向查找
  • 用 INDEX+MATCH 实现行、列双向定位的万能查询
  • 会用 COLUMN 和 MATCH 嵌套 VLOOKUP，一次拖出多列结果
  • 了解 INDEX 引用图片的进阶玩法

知识点讲解

MATCH：只报告位置，不取值

MATCH 回答的问题是「它在第几个」：返回查找值在查找区域中的位置序号。

语法：=MATCH(lookup_value,lookup_array,[match_type])。参数 1 是要找的值；参数 2 是在哪一行或哪一列里找（可以不是整行整列）；参数 3 写 0 是精确查找，写 1 是模糊查找。

进销存试一把：=MATCH("晨光中性笔 0.5",商品档案!B:B,0) 返回 5，意思是这个名称排在商品档案 B 列的第 5 个位置。它自己不取值，但这个位置正是下一节 INDEX 要的原料。

=MATCH("晨光中性笔 0.5",商品档案!B:B,0)

在商品档案 B 列里精确查找名称，返回它在区域中的位置序号——记住是序号，不直接等于行号。

💡 提示:

  • match_type 写 0 精确、写 1 模糊；业务查找一律写 0
  • MATCH 给的是区域内的相对位置，区域从第 2 行开始时，位置 5 实际在第 6 行

INDEX：报位置，取值

INDEX 回答的是「第几个是什么」：返回区域中指定行、指定列的值。

语法：=INDEX(array,row_num,[column_num])。参数 1 是引用哪块区域；参数 2 是第几行；参数 3 是第几列，区域只有一列时可省略。

它自己不会找，你给位置它给值——和 MATCH 恰好互补：一个会找不会拿，一个会拿不会找。

=INDEX(商品档案!A:A,5)

取商品档案 A 列第 5 个单元格的值——单列区域时第 3 参数省略。

💡 提示:

  • 用整列引用时，序号恰好等于行号，公式更好懂

黄金组合：INDEX+MATCH 反向查找

把两者拼起来：MATCH 负责找位置、INDEX 负责按位置取值，查找和引用彻底分开，不存在 VLOOKUP「返回列必须在查找列右边」的限制——VLOOKUP 只能从左往右查，它能从右往左。

进销存刚需场景：客户电话报来商品名称「晨光中性笔 0.5」，要反查编号。VLOOKUP 干瞪眼（编号在名称左边），INDEX+MATCH 一条搞定：=INDEX(商品档案!A:A,MATCH(D2,商品档案!B:B,0))。

记忆口诀：INDEX（要哪列的值），MATCH（按哪列找谁）。

=INDEX(商品档案!A:A,MATCH(D2,商品档案!B:B,0))

内层 MATCH 在名称列找到 D2 的位置，外层 INDEX 去编号列取同位置的值——名称进、编号出，方向随意。

💡 提示:

  • 查找列和返回列想怎么组合就怎么组合，这是它比 VLOOKUP 强的根本原因

COLUMN：把列号变成参数

=COLUMN() 不带参数时返回当前单元格在第几列；括号里也可以写单元格编号，如 =COLUMN(D5) 返回 4。

它最大的价值是嵌进 VLOOKUP 的第 3 参数，让公式向右拖拽时自动变换返回列——原本第 3 参数是个死数字，拖拽不更新，只能一列列手工改。

=COLUMN()

返回当前单元格的列号：写在 E 列就是 5。

=COLUMN(D5)

返回指定单元格的列号，D5 是第 4 列。

💡 提示:

  • 同理还有 ROW() 返回行号，第 17 讲做「按位置找规律」的引用时还会重用

VLOOKUP 嵌套 COLUMN/MATCH：一次拖出多列

VLOOKUP 的第 3 参数拖拽时不自动变，两招破解。

第一招：查询表的列顺序与源表完全一致时，嵌套 COLUMN() 按需加减偏移。比如公式写在第 5 列而返回列是区域第 2 列，就写 COLUMN()-3，右拖自动递增。讲义强调：前提是两边表头顺序一致。

第二招：顺序不一致时嵌套 MATCH，让查询表的表头自己去源表里定位列号：=VLOOKUP($A2,商品档案!$A:$H,MATCH(B$1,商品档案!$A$1:$H$1,0),0)。锁定要点：编号列锁列（$A2），表头锁行（B$1）。

=VLOOKUP($A2,商品档案!$A:$H,MATCH(B$1,商品档案!$A$1:$H$1,0),0)

MATCH 拿查询表表头 B1 去源表表头行定位列号交给 VLOOKUP：表头顺序怎么排都能一次拖出全部字段。

💡 提示:

  • 查询表的表头文字必须与源表完全一致，多一个空格都定位不到
  • 拖拽前先检查 $ 的位置：数据锁列、表头锁行

进阶玩法：INDEX 还能引用图片（了解即可）

给商品档案配上照片，查询时照片跟着编号变——靠的就是 INDEX 也能返回「图片引用」。

步骤：① 把各商品照片按编号排好放入工作表；② 点【公式-定义名称】新建名称（如「商品图片」），引用位置输入 INDEX+MATCH 函数定位到对应照片；③ 添加「照相机」功能：【文件-选项-自定义功能区】里把它加到新建选项卡，选中接收单元格点「照相机」画一个框，在编辑栏输入 =商品图片 回车；也可以复制图片到接收位置后，选中图片在编辑栏输入 =商品图片。

操作环节多、依赖照相机功能，先理解思路，期末做查询界面时有兴趣再试。

💡 提示:

  • 图片引用依附于工作簿结构，行列变动容易失效，重要文件慎用

实战演练

系统建到这一讲，该给不懂函数的同事一个「查询台」了：他们只要在格子里选商品、选字段，结果自己跳出来。这一讲新建「查询」工作表，用 INDEX+MATCH 搭三个模块——按名称反查编号的反向查询、编号×字段的双向查询、表头驱动一次拖多列的批量查询。做完它，你的进销存系统第一次有了「人机界面」的意思。可先在右侧练习数据里试手感，再回到进销存工作簿正式建造。

  1. 反向查询区：名称换编号 — 新建工作表「查询」。B2 输入商品名称（如 晨光中性笔 0.5）；B3 输入 =INDEX(商品档案!A:A,MATCH(B2,商品档案!B:B,0)) 得到编号；B4 再来一条 =INDEX(商品档案!E:E,MATCH(B2,商品档案!B:B,0)) 带出进价。换个名称试试，三个格子一起刷新。
  2. 双向查询区：行和列都由 MATCH 找 — B7 放商品编号（手工填一个），B8 放字段名（如 当前库存），B9 输入 =INDEX(库存台账!$A$2:$J$31,MATCH(B7,库存台账!$A$2:$A$31,0),MATCH(B8,库存台账!$A$1:$J$1,0))——第几行由编号决定，第几列由字段名决定，台账里任何数据都能定位。
  3. 给字段名挂上下拉 — 选中 B8，【数据-数据验证】，允许选「序列」，来源框选库存台账 A1:J1 的表头区。现在字段名变成下拉菜单，选「累计入库」「累计出库」「当前库存」，B9 跟着变。
  4. 批量查询区：VLOOKUP+MATCH 一次拖多列 — D2:E2 写表头「进价」「售价」（顺序故意与源表不同），D3 输入 =VLOOKUP($B$7,商品档案!$A:$H,MATCH(D2,商品档案!$A$1:$H$1,0),0)，右拖到 E3——表头顺序乱了也照样查对。
  5. 压力测试与防弹衣 — 把 B7 改成不存在的编号 XX999，观察各查询区报 #N/A；再用 IFERROR 包住双向查询：=IFERROR(原公式,"查无此品")，确认错误显示成友好提示。
  6. 选做：跨档案查客户 — 加一个客户查询：F2 输入客户编号，F3 输入 =INDEX(客户档案!B:B,MATCH(F2,客户档案!A:A,0)) 带出公司名称——体会与 VLOOKUP 写法的差异：要哪列、按哪列，一目了然。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 反向查询：输入任一商品名称都能得到编号和进价
  • ☐ 双向查询：字段下拉切换时结果正确变化（抽查 2 个字段）
  • ☐ 批量查询：D3 右拖到 E3 结果正确，证明 MATCH 嵌套生效
  • ☐ 不存在编号显示「查无此品」而非 #N/A
  • ☐ 能口头说出 INDEX+MATCH 比 VLOOKUP 多解决了什么（反向查找、列序自由）