INDEX 加 MATCH：更稳的查找组合

一、先认识 INDEX：按坐标取值

=INDEX(区域,行号,[列号]) 的作用是按坐标从区域里取值。给一个区域和行号列号，它就把交叉点上的值交给你。写成 =INDEX(B2:D100,5,2) 会返回 B2:D100 这块区域里第 5 行第 2 列的值，也就是 C6 单元格。

行号与列号都是相对于区域而言的，不是工作表的行号列号。这一点和 VLOOKUP 的列序号一样，初学时容易混淆。写成 =INDEX(B2:D100,1,1) 返回 B2，因为 B2 是这块区域的左上角。

第三参数可以省略，但省略的前提是区域只有一列或只有一行。写成 =INDEX(B2:B100,5) 返回第 5 行的值，写成 =INDEX(B2:D2,3) 返回第 3 列的值。区域是二维时省略第三参数会返回错误值，因为 Excel 不知道你要哪一列。

行号或列号写成 0 时有特殊含义：=INDEX(B2:D100,0,2) 返回整列，=INDEX(B2:D100,5,0) 返回整行。这个特性在动态数组环境下能一次溢出出整行整列的数据，老版本里则需要配合数组公式使用。日常用得不多，但知道它存在能在某些场景省很多事。

INDEX 还有另一种写法，也就是引用形式：=INDEX(区域1,行号,[列号],[区域编号])，可以在多个区域之间切换。这种写法极少用到，知道即可，不必深究。

INDEX 最重要的特性是它返回的是引用而不是值。这意味着它可以嵌套在其他需要区域的函数里，比如 =SUM(INDEX(...):INDEX(...)) 能构造一个动态区域。这个能力是 VLOOKUP 不具备的，也是高级用法的基石。

二、再认识 MATCH：按内容找位置

=MATCH(查找值,查找区域,[匹配方式]) 返回查找值在区域中的相对位置，是一个数字。写成 =MATCH("市场部",B2:B100,0) 会返回市场部在 B2:B100 里是第几行，如果它在 B5，结果就是 4。

第三参数有三种取值：0 表示精确匹配，1 表示近似匹配且要求升序，-1 表示近似匹配且要求降序。日常一律用 0，用法与 VLOOKUP 的第四参数一致，同样不能省略，因为省略时默认为 1。

MATCH 的查找区域必须是单行或单列，不能是二维区域。写成 =MATCH("市场部",A1:D1,0) 是在一行里横向查找，返回列号；写成 =MATCH("市场部",A1:A100,0) 是在一列里纵向查找，返回行号。方向由区域的形状自动决定，不需要额外参数。

MATCH 支持通配符，问号代表单个字符，星号代表多个字符。写成 =MATCH("北京*",A2:A100,0) 会返回第一个以北京开头的项所在的位置。注意通配符只在精确匹配模式下生效，也就是第三参数为 0 时。

查找不到时 MATCH 返回 #N/A。这个错误值会沿着公式链向上传播，所以最终呈现时通常需要用 IFERROR 兜底。判断是否存在某个值，写 =ISNUMBER(MATCH(...)) 比直接看结果更规范，因为 TRUE 或 FALSE 可以直接用在 IF 里。

MATCH 还有一个优势：它对区域方向不敏感，横向纵向都能用。这让它天然适合做二维查找里的两次定位，一次找行、一次找列，交给 INDEX 取值。这也是所谓二维查找的标准解法。

MATCH 的查找区域也不建议包含合并单元格，理由与 VLOOKUP 相同：合并后只有左上角有值，其余为空，位置计算容易错乱。参数表与查找列请一律保持一维结构。

还有一个提高可读性的小技巧：给区域起名字。把 B2:B100 命名为姓名，把 C2:C100 命名为工号，公式写成 =INDEX(姓名,MATCH(A2,工号,0))，读起来几乎是一句话。命名区域自带绝对引用，也省掉了 dollar 符号，在复杂表里能明显降低出错率。

三、为什么它比 VLOOKUP 更稳

把两者组合起来，写成 =INDEX(结果列,MATCH(查找值,查找列,0))。例如要根据工号查姓名，写成 =INDEX(B2:B100,MATCH(A2,C2:C100,0))，其中 B 列是姓名、C 列是工号。查到的位置交给 INDEX 去取值，逻辑清晰。

第一点稳定性：不受方向限制。查找列可以在结果列的左边、右边，甚至相隔很远，都不影响。这直接解决了 VLOOKUP 不能向左查的问题，不必再为了配合函数去调整表格结构。

第二点稳定性：插入删除列不会失效。VLOOKUP 的列序号是写死的数字，中间插一列就取错字段；INDEX 加 MATCH 引用的是具体的列区域，插入列时 Excel 会自动调整引用，公式永远指向正确的那一列。这是长期维护中最关键的优势。

第三点稳定性：大表下更快。VLOOKUP 需要把整个区域载入再定位，INDEX 加 MATCH 只扫描查找列和结果列两列。在十万行级别的数据上，这个差距会被放大，尤其在有很多条查找公式时。

第四点稳定性：可以一次做二维定位。写成 =INDEX(数据区,MATCH(行条件,行标题,0),MATCH(列条件,列标题,0))，一次定位到交叉点。这是交叉表查询的标准解法，VLOOKUP 要配合 MATCH 才能勉强实现，写法还更绕。

唯一的缺点是公式更长、概念更多，需要理解两个函数才能看懂。这也是为什么很多教程仍然先教 VLOOKUP。但只要你打算长期用 Excel，INDEX 加 MATCH 是必须跨过的一道坎。两种查找的对照可以回看 VLOOKUP 与 HLOOKUP：老牌查找。

四、二维查找与动态列

二维查找的标准写法是两次 MATCH。假设 A2:A50 是产品名，B1:M1 是月份，B2:M50 是数据区。要查某产品在某月的数值，写成 =INDEX($B$2:$M$50,MATCH($P$2,$A$2:$A$50,0),MATCH($Q$2,$B$1:$M$1,0))，其中 P2 是产品名、Q2 是月份。

两个 MATCH 的区域方向必须一竖一横，第一个返回行号、第二个返回列号。写反了会返回错误值或者莫名其妙的数。检查方法很简单：选中第一个 MATCH 的单独结果看是不是行号范围内的数，第二个同理。

动态取列的写法也很常见。要根据标题名取对应列的数据，写成 =INDEX($B$2:$M$50,ROW(A1),MATCH($Q$2,$B$1:$M$1,0))，向下填充时用 ROW(A1) 生成递增的行号。或者用 VLOOKUP 配合 MATCH 定位列号，写成 =VLOOKUP($P$2,$A$2:$M$50,MATCH($Q$2,$A$1:$M$1,0),0)，两种写法都可以。

更高级的用法是 INDEX 返回引用构造动态区域。写成 =SUM(INDEX(C:C,MATCH("开始",A:A,0)):INDEX(C:C,MATCH("结束",A:A,0)))，用两个 INDEX 定位起点与终点，中间用冒号连接，得到一个会随标记行变化的求和区域。这类写法在数据区位置不固定的报表里非常有用。

还有一类需求值得单独提：按最接近的值查找。比如要找不高于预算的最高配置，写成 =INDEX(B2:B20,MATCH(F2,A2:A20,1))，其中 A 列是价格且已升序排列，F2 是预算。MATCH 的第三参数写 1 做近似匹配，会返回小于等于预算的最大价格所在的位置。这类查找在选型、配货场景里很常见。

与之相对的是找不低于某值的最小项，写法是先筛选出满足条件的行，再用 =INDEX(...,MATCH(TRUE,INDEX(A2:A20>=F2,0),0)) 定位第一个为真的位置。公式略绕，实际中更简单的做法是加辅助列把条件结果落地，再用 MATCH 查找 TRUE。

五、真实场景

场景一：反向查找。价格表里 A 列是价格、B 列是型号，要按型号查价格，也就是从右往左查。VLOOKUP 做不到，写成 =INDEX(A2:A100,MATCH(D2,B2:B100,0)) 即可，其中 D2 是要查的型号。这是 INDEX 加 MATCH 最经典的应用场景。

场景二：交叉表查询。销售表里产品名在 A 列、月份在第一行，要查某产品某月的销量，用前面讲的两次 MATCH 写法。把产品名和月份分别放在两个单元格里，配合下拉菜单，就做成了一个自助查询工具，不懂公式的同事也能用。

场景二的补充：交叉表查询做自助工具时，建议给查询单元格加数据验证下拉。产品名列引用 A2:A50 做下拉，月份列引用 B1:M1 做下拉，用户只能选不能手输，从源头杜绝拼错。再给结果单元格套一层 IFERROR，整个工具就不会出现任何错误值，交付给业务同事非常省心。

场景三：多条件查找。用 INDEX 配合 SUMPRODUCT 或 MATCH 的数组写法。写成 =INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))，MATCH 查找值为 1，也就是两个条件同时成立的那一行。这个公式在老版本需要三键确认，新版可以直接回车。

场景四：取最后一个非空值。写成 =INDEX(C:C,MATCH(9.99E+307,C:C,1))，用一个极大的数做近似匹配，返回最后一个数值所在的行。如果要取最后一个文本，写成 =INDEX(A:A,MATCH("座",A:A,1))，用编码极大的汉字做近似匹配。这两个写法在取最新记录时极为实用。

场景五：动态标题取数。报表标题经常变动，写死的列号会失效。写成 =INDEX($A:$M,ROW(),MATCH("本月销售额",$1:$1,0))，用 MATCH 在第一行找标题位置，无论列怎么挪都能取到正确的数据。这一招在对接别人做的、经常调整结构的报表时能救命。

场景六：排名榜。先用 LARGE 取第 N 名的数值，再用 INDEX 加 MATCH 反查姓名，写成 =INDEX(A2:A200,MATCH(E2,C2:C200,0))，其中 E2 是 LARGE 的结果。极值与查找的组合用法见 极值与排名：MAX、MIN、LARGE、SMALL、RANK。

六、把组合公式写对的自查步骤

写 INDEX 加 MATCH 有一条固定的流程，照着做基本不会错。第一步，先单独写 MATCH，确认它返回的数字等于你期望的行号。第二步，写一个同样形状的 INDEX，用手写数字测试取值是否正确。第三步，把 MATCH 套进 INDEX 替换那个数字。分步验证比一次性写完再调试快得多。

第二步里的手写数字测试最容易被跳过，却最有价值。假设 MATCH 应该返回 5，那就先写 =INDEX(B2:B100,5) 看结果是不是你想要的那个单元格。如果这步就错了，说明区域起点或范围有问题，先修它再谈其他。

第三步之后再加兜底。写成 =IFERROR(INDEX(B2:B100,MATCH(A2,C2:C100,0)),"未找到")，或者返回 0 以便后续求和。兜底不是可选项，因为查找类公式在大批量数据下几乎必然会遇到个别找不到的记录，一条错误值就足以毁掉整列的求和结果。

最后一步是填充检查。向下填充前，确认所有区域都加了美元符号，尤其是 MATCH 的查找区域。写 =INDEX($B$2:$B$100,MATCH(A2,$C$2:$C$100,0))，只有 A2 是相对引用，其他全部锁定。填充后抽查最后一行，看公式是否仍然指向正确的区域。

常见问题

想返回倒数第一个非空值，除了那个极大数写法还有别的吗

有，而且更好读。用 =LOOKUP(2,1/(C:C<>""),C:C)，先构造一个由 1 和错误值组成的数组，LOOKUP 忽略错误值并取最后一个 1 的位置，从而得到最后一个非空值。这个写法不需要依赖 9.99E+307 这种魔数，也不需要排序，兼容性与可读性都更好，是取最新记录的推荐方案。

二维查找里月份是文本而标题是日期，为什么查不到

因为类型不一致。标题行里的月份看起来是 1 月、2 月，实际可能是日期值，而查询单元格里是纯文本。解决办法是统一格式：要么把标题改成文本，要么查询值写成 =DATE(2026,1,1) 并用相同的显示格式。判断类型的办法是用 =ISNUMBER() 分别检查两边，FALSE 与 TRUE 不一致就说明有问题。

MATCH 返回的是工作表的行号吗

不是，它返回的是相对于查找区域的位置。区域写成 C2:C100 时，返回 1 意味着是 C2，也就是工作表的第 2 行。这个差异常常导致 INDEX 取到错位的行，尤其是当 INDEX 的区域起点与 MATCH 的区域起点不一致时。避免出错的关键习惯：让 INDEX 的区域与 MATCH 的区域起点相同、行数相同，例如都是第 2 行到第 100 行。

为什么组合公式返回了错误值

最常见的原因是两个函数的区域起点不一致。写 =INDEX(B2:B100,MATCH(A2,C1:C100,0)) 时，MATCH 的区域从第 1 行开始而 INDEX 从第 2 行开始，位置整体错位一行。第二常见的原因是 MATCH 没找到，返回错误值后整个公式跟着出错，用 IFERROR 兜底即可。第三个原因是第三参数写成了 1 或省略，导致近似匹配。

多条件查找一定要用数组公式吗

不一定。最简单的做法是加一列辅助列，把多个条件拼成一个键，写成 =A2&"|"&B2，然后 =INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))。辅助列方案兼容性最好，调试也方便。数组写法 =INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0)) 更紧凑，但在老版本需要三键确认，且数据量大时较慢。

有重复值时，MATCH 返回哪一个

返回第一个匹配到的位置，后面的重复项全部忽略，而且不会提示。这是查找类函数的共同行为。如果重复值不可接受，先在主数据里用 =COUNTIF(A:A,A2) 检查并清理；如果需要返回所有匹配项，改用 FILTER 函数，写成 =FILTER(C2:C100,A2:A100=F2)，一次返回所有结果并自动溢出。

能不能用它做区间查找

可以。MATCH 的第三参数写成 1 时做近似匹配，返回小于等于查找值的最大项的位置，前提是查找列已按升序排列。写成 =INDEX(B2:B5,MATCH(C2,A2:A5,1))，A 列是区间下限，B 列是对应等级。这与 VLOOKUP 的近似匹配完全等价，同样要注意排序与首档覆盖最小值。

WPS 里这个组合能用吗

完全能用。INDEX 与 MATCH 都是很早就有的函数，WPS 表格全版本支持，行为与 Excel 一致。需要留意的仍然是数组写法的三键确认问题，以及 FILTER 这类新函数的版本支持。写要发给多人的文件时，多条件查找优先用辅助列方案，兼容性最有保障。

有没有比这更简单的新写法

有，XLOOKUP 就是为了解决这些麻烦而设计的，一个函数替代整个组合，还自带找不到时的兜底参数。但它只在 Excel 2021、365 与较新的 WPS 里可用。如果你确定所有使用者的软件版本都够新，可以直接用 XLOOKUP；否则 INDEX 加 MATCH 仍是最稳的通用方案。两者对比见 XLOOKUP 与 IFERROR：新写法和兜底。