Index Match Excel：更高效的数据查找方式

INDEX MATCH 是将 Excel 的两个函数 INDEX() 和 MATCH() 组合使用的方式，是常用函数 VLOOKUP() 的一种替代方案。

在本指南中，我将带您了解 INDEX() 和 MATCH() 各自以及协同工作的原理，如何执行纵向与横向查找，以及使用 INDEX MATCH 的最佳实践与实际示例。

什么是 Excel 中的 INDEX MATCH？

INDEX MATCH 是对这两个函数嵌套用法的简写，相当于 INDEX(MATCH()) 的思路。下面分别看看它们单独和组合时如何工作。

Excel 中的 INDEX() 函数

Excel 中的 INDEX() 函数会从指定范围或表中返回某个值或对该值的引用。它有两种用法：数组形式和引用形式。

数组形式

当您处理固定的表或数组时，可使用数组形式。在这种格式中，INDEX() 会根据您指定的行号和列号提取一个值。其语法为：

INDEX(array, row_num, [column_num])技术支持

参数说明如下：

• array （必需）指您要处理的数值范围或数组。

• row_num （必需）指要返回值的行号。

• column_num （可选）指要返回值的列号。

我们至少需要指定 row_num 或 column_num 之一，以便 Excel 能定位到该值。

不过，使用此语法时需注意以下要点：

• 如果数组只有一行或一列，可以省略 row_num 或 column_num。

• 如果同时提供了 row_num 和 column_num，INDEX 会返回两者交叉处的值。

• 如果将 row_num 或 column_num 设为 0，Excel 会返回整列或整行（需要作为数组公式输入才能看到结果）。

引用形式

当您处理多个范围并需要指定要查看哪一个时，可以使用引用形式。

其语法为：

INDEX(reference, row_num, [column_num], [area_num])技术支持

参数说明如下：

• reference （必需）用于引用一个或多个单元格区域。

• row_num （必需）是所选区域中的行号。

• column_num （可选）是所选区域中的列号。

• area_num （可选）当存在多个范围时，告诉 Excel 使用哪一个。

另有几点需要记住：

• 如果省略 area_num，Excel 默认使用第一个范围。

• 此形式中不能混合来自不同工作表的范围，否则会报错。

• 如果同时省略行和列，会返回指定的整个区域。

Excel 中的 MATCH() 函数

MATCH() 函数可帮助您在某一行或列中找到某个值的位置。它不会返回该值本身，而是告诉您该值在列表中的位置。

其语法为：

MATCH(lookup_value, lookup_array, [match_type])技术支持

参数说明如下：

• lookup_value（必需）是您要查找的值。可以是数字、文本、逻辑值（TRUE/FALSE），或对某个单元格的引用。

• lookup_array（必需）是 Excel 将要搜索的单元格范围。

• match_type（可选）定义 Excel 如何匹配。默认值为 1。其中 1 查找小于或等于 lookup_value 的最大值（要求升序）；0 查找精确匹配（任意顺序）；-1 查找大于或等于 lookup_value 的最小值（要求降序）。

MATCH 函数不区分大小写，因此会将大写和小写视为相同。如果处理文本并希望使用通配符，如 *（任意数量字符）或 ?（单个字符），请将 match_type 设为 0，因为只有该模式支持通配符搜索。

INDEX() 与 MATCH() 在 Excel 中如何协同工作？

MATCH() 最常见的用法之一就是与 INDEX() 配合使用。MATCH() 用于找到值的位置，INDEX() 则返回该位置上的值。

将 MATCH() 嵌入 INDEX() 中，可以得到在列或行随时间移动时仍具备动态性和鲁棒性的公式。

例如，我有一份示例数据集。如果我想查找 A 列中的 Banana 所在行在 B 列中的对应内容，我可以像这样组合使用 INDEX MATCH：

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))技术支持

这里，MATCH("Banana", A2:A5, 0) 会在 A 列中找到 Banana 的位置，而 INDEX(B2:B5, ...) 则返回同一行在 B 列中的值。

INDEX MATCH 与 VLOOKUP()：该用哪个？

INDEX MATCH 能完成一些 VLOOKUP() 做不到的任务。不过，如何选择取决于您在 Excel 表中所需的灵活性与性能。我们来比较一下。

列的灵活性与数据完整性

VLOOKUP() 的一大缺点是依赖固定的列序号。如果插入或移动列，您的公式可能会失效。

例如：

如果在 A 列与 B 列之间新增一列，第三列会发生偏移，公式会返回错误结果，如下图所示。

=VLOOKUP("Apple", A2:C5, 2, FALSE)技术支持

INDEX MATCH 通过处理定义好的范围，而不是硬编码列位置，从而避免了这个问题：

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))技术支持

即使列被重新排列，此公式仍然有效，更可靠且更易维护。此处在 B 列前插入了一列；公式会自动更新，结果依旧正确。这就是 INDEX MATCH 的妙处。

查找方向与通用性

VLOOKUP() 只能从左向右查找。这意味着查找列必须始终位于范围的第一列。如果所需的值位于查找列左侧，VLOOKUP() 无法找到。

不过，如果按水果名称向右查找颜色，该公式可以工作。例如，若要查找列表中香蕉的颜色，它会返回准确结果：

而使用 INDEX MATCH 则没有这种限制。您可以向任意方向查找：左、右、上或下。它还支持水平查找，让您对数据组织方式有更大掌控力。

让我们将 INDEX MATCH 应用于此示例，看看它如何同时支持左查找和右查找。

对于左查找，您可以使用以下公式，它会给出准确结果：

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))技术支持

对于右查找，您可以使用以下公式，同样会返回正确结果：

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))技术支持

Excel 中的性能与效率

VLOOKUP() 会一次性扫描整张表来找到匹配并返回值。而 INDEX MATCH 将任务拆分：MATCH() 找到行或列，INDEX() 返回对应的值。

这让它在复杂或大体量的工作簿中更快、更省内存。

下表可以帮助您更好地了解各函数支持的功能：

何时使用 INDEX MATCH

在以下场景，您可以使用 INDEX MATCH：

• 处理大型数据集，对速度有要求
• 模型具有动态性，会新增或重排列
• 需要向左或在水平范围中进行查找

如果您的 Excel 文件较小且简单，VLOOKUP() 也能胜任。但对于高级建模和报告，INDEX MATCH 更稳妥。

INDEX MATCH 分步使用方法

假设您有两列：Fruits（水果） 和 Sales（销量）。您想找到 “Apple” 的销量数值。

为此，输入组合的 INDEX MATCH 公式并按下 Enter：

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))技术支持

这里，MATCH("Apple", A2:A6, 0) 会在 Fruits（水果） 列表中找到 “Apple” 的位置。而 INDEX(B2:B6, ...) 则用该位置从 Sales（销量） 列返回对应的销量数值。

您可以看到公式返回了 12000，这就是 Apple 的销量。因此，这就是您的结果。

避免 INDEX MATCH 错误

尽管 INDEX MATCH 是很好的组合，但如果设置不当，仍可能报错。以下是如何排查最常见问题，并避免破坏公式的错误。

#N/A 错误及其解决方法

#N/A 错误表示 Excel 找不到您要查找的值。常见原因包括：

• 拼写错误、额外空格或文本不一致（例如 "apple" 与 "Apple"）

• 查找范围中没有该值

• 错误的 match_type，例如需要精确匹配却使用了 1 而不是 0

• 数据中存在隐藏字符（如不换行空格）

有两种方法可以解决：

使用 TRIM() 清理前后空格：

=MATCH(TRIM("Apple"), A2:A6, 0)技术支持

加入 IFERROR()，用备用值替代错误：

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")技术支持

INDEX MATCH 公式中的 #REF! 错误

#REF! 错误通常指向断开的引用，常见原因包括：

• INDEX() 与 MATCH() 的范围行数不一致

• 公式中使用的行或列被删除

• 复制粘贴公式后未更新单元格引用

可以通过以下方法轻松修复：

• 确保 INDEX() 与 MATCH() 的范围大小一致：

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))技术支持

• 用 绝对引用 （如 $A$2:$A$6）锁定范围，保持复制粘贴时公式稳定。

• 避免删除公式依赖的单元格。

性能缓慢与大型数据集

当您处理成千上万行数据时，未优化的 INDEX MATCH 公式可能会拖慢 Excel。

优化方法如下：

• 限制查找范围，避免引用整列：

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))技术支持

• 避免在查找附近使用易变函数 OFFSET()、INDIRECT() 和 NOW()。

• 使用 Excel 表或命名范围，保持有序并提升性能。

在 Excel 365 中处理溢出错误

Excel 365 引入了动态数组，结果可能“溢出”到相邻单元格。如果该过程出现问题，您将看到 #SPILL! 错误。

常见原因包括：

• 相邻单元格非空。
• 公式要返回多个值，但溢出区域被阻塞或为合并单元格。
• 在只需要单个值的场景却使用了动态数组。

这些问题都可修复。解决方法如下：

• 清空公式下方或旁边的单元格。
• 取消溢出区域内的合并单元格。
• 若只需一个值，可用 @ 强制返回单一结果：

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))技术支持

总结

如果您一直依赖 VLOOKUP()，现在是时候转向更稳健的方案了。您可以先在工作文件的副本中，从几个简单的查找开始使用 INDEX MATCH。尝试在因列变化而出错的地方或需要更高灵活性的数据结构中替换为新公式。看到它干净利落地处理这些场景后，您就很难再回头了。