Excel小技巧:INDEX+MATCH,永远不会因为插行而崩的查找方案

你花了半天写了一整列 VLOOKUP 公式，数据跑得顺顺的。然后领导过来说：“帮我在第三行前面插一行，加个汇总标题。”

你一插，整列公式全变成了 #REF!。

为什么？因为 VLOOKUP 的第三个参数是"第几列"。你在公式里写了个数字 3，意思是从查找区域的第 3 列取值。但你往前面插了一列之后，原来第 3 列变成了第 4 列，公式还写着 3，当然就取错了。更惨的是它不会报错，只是悄悄给你一个错误的值，你不核对根本发现不了。

这种坑，INDEX + MATCH 组合可以从根本上避免。

01、先搞清楚这两个函数分别干什么

别被"组合"两个字吓到。拆开看，每个函数都极其简单。

MATCH 做的事：告诉你"你要找的东西在第几行"。

比如你在 A 列找"张三"，MATCH 会返回一个数字，告诉你"张三在 A 列的第 5 行"。它只负责定位，不负责取值。

语法：

=MATCH(查找值, 查找范围, 0)

最后的 0 表示精确匹配，记住写 0 就行。

INDEX 做的事：从一列数据里，按行号取出对应的值。

你告诉 INDEX “从 D 列取第 5 行的值”，它就把 D5 的值给你。

语法：

=INDEX(返回范围, 行号)

把两个函数拼起来，逻辑就变成了：先用 MATCH 找到行号，再用 INDEX 按行号取值。

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

翻译成人话就是：去"查找范围"里找到目标在第几行，然后从"返回范围"的同一行把值取出来。

02、实际用起来长什么样

还是用昨天的例子。A 列工号，B 列姓名，C 列部门，D 列工资。你知道工号"10086"，要查工资。

VLOOKUP 的写法：

=VLOOKUP("10086", A:D, 4, 0)

INDEX + MATCH 的写法：

=INDEX(D:D, MATCH("10086", A:A, 0))

看起来好像多了几个字，但它带来了三个 VLOOKUP 做不到的能力。

第一个优势：不怕插行插列

这是今天要讲的核心。

VLOOKUP 用"第几列"来定位返回列，你插一列，这个数字就废了。INDEX + MATCH 根本不用"第几列"这个概念。它直接写明了"查找范围是 A 列，返回范围是 D 列"，两个范围是独立的。你在 A 列和 D 列之间插多少列都不影响，因为 A 列还是 A 列，D 列还是 D 列。

同理，插行也不怕。MATCH 返回的是"目标在当前范围内的相对位置"，范围整体移动了，相对位置不变。

一句话总结：VLOOKUP 靠"数格子"定位，格子变了数就错；INDEX + MATCH 靠"指名字"定位，名字不会变。

第二个优势：可以从右往左查

VLOOKUP 的老毛病了：查找列必须在数据区域的最左边。如果你知道姓名想反查工号，VLOOKUP 做不到，因为工号在姓名的左边。

INDEX + MATCH 完全不受这个限制。查找范围和返回范围可以任意摆放：

=INDEX(A:A, MATCH("张三", B:B, 0))

从 B 列找姓名，返回 A 列的工号。从左往右、从右往左、隔着几列查，都行。

第三个优势：查找范围和返回范围可以不同大小

VLOOKUP 要求查找区域是一个矩形，查找值在第一列，返回值在第 N 列，行数必须一致。

INDEX + MATCH 的两个范围可以大小不同。查找范围可以是 A2:A100（只有工号列），返回范围可以是 D5:D103（工资列从第 5 行开始）。只要两个范围的行数一致，MATCH 返回的相对位置就能正确映射到 INDEX 的范围上。

这在处理合并单元格、不规整表格、或者查找列和返回列起始行不同的场景中特别有用。

03、日常使用中的几个注意事项

第一，两个范围的行数要对齐。 查找范围写了 A2:A100（99行），返回范围也得是 99 行，比如 D2:D100。如果一个写了 99 行一个写了 100 行，MATCH 返回的第 50 行，在 INDEX 那边可能取到的就是第 51 行的值，差一行错一个数。

第二，MATCH 的第三个参数别忘了写 0。 不写 0 或者写 1，MATCH 会按近似匹配来找，要求数据必须提前排好序，否则返回的结果可能不对。日常 99% 的场景都应该写 0（精确匹配）。

第三，找不到时还是会报错。 和 VLOOKUP 一样，如果查找值不存在，INDEX + MATCH 也会返回 #N/A。外面套一层 IFERROR 处理：

=IFERROR(INDEX(D:D, MATCH("10086", A:A, 0)), "未找到")

这样找不到时就显示"未找到"，而不是一个刺眼的错误值。

第四，整列引用 vs 精确范围。 上面例子为了简单用了 A:A、D:D 这种整列引用。数据量大的时候整列引用会拖慢速度。建议写成精确范围，比如 A2:A500、D2:D500。

和 VLOOKUP 的正面对比

说实话，如果你的表结构稳定、从来不变，VLOOKUP 完全够用。但现实中谁的表不变呢？今天加一列备注，明天插一行标题，后天调整一下字段顺序。VLOOKUP 在这种环境下就像定时炸弹，而 INDEX + MATCH 给你的是确定性。

04、多条件查找的进阶用法

单条件查找学会之后，很多人会问：如果要根据两个条件查怎么办？比如根据"部门+岗位"查工资。

这里介绍一种写法，用数组公式的思路：

=INDEX(D:D, MATCH(1, (A:A="销售部")*(B:B="经理"), 0))

(A:A=“销售部”) 会返回一列 TRUE/FALSE，(B:B=“经理”) 也返回一列 TRUE/FALSE。两个相乘，只有两个条件同时满足的那一行才是 1，其余都是 0。MATCH 找 1 的位置，就是目标行。

注意：这个写法在旧版 Excel（2019 之前）中需要按 Ctrl+Shift+Enter 确认为数组公式，公式两边会自动加上花括号 {}。Excel 365 和 2021 不需要，直接回车就行。

05、今天的总结

INDEX + MATCH 的核心逻辑就三步：

第一步，MATCH 在查找范围里找到目标的行号。

第二步，INDEX 按这个行号从返回范围里取值。

第三步，两个范围独立指定，不依赖"第几列"。

如果你现在的项目里全是 VLOOKUP，不用一口气全改。先在新写的公式里用 INDEX + MATCH，慢慢替换。等到下次有人插了一行、公式没崩的时候，你会感谢今天的自己。

——E晓分享，共同进步——