INDEX MATCH：更强大的查找方法

如果你一直在 Excel 中使用 VLOOKUP 处理所有查找任务，你并不孤单——它是电子表格领域最广为人知的函数之一。但有经验的 Excel 用户几乎无一例外地会进阶到 INDEX MATCH，这是一种由两个函数组合而成的方法，更灵活、更可靠，能够解决 VLOOKUP 根本无法应对的问题。本文将结合真实语法、详细示例和可立即上手的实操演练，带你彻底搞清楚其中的原因。

INDEX 和 MATCH 分别是什么？

在将两者组合使用之前，先分别了解每个函数会更有帮助。

INDEX 函数

INDEX 返回区域或数组中指定位置的单元格值。

=INDEX(array, row_num, [col_num])

• array — 要查找的单元格区域
• row_num — 该区域内的行位置
• col_num — （可选）该区域内的列位置

例如，=INDEX(A1:A10, 3) 返回 A 列第 1 行到第 10 行中第三行的值。

MATCH 函数

MATCH 在区域内搜索某个值，并返回其位置编号——不是值本身，而是告诉你它所在位置的数字。

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value — 要搜索的值
• lookup_array — 要搜索的单行或单列
• match_type — 使用 0 表示精确匹配（最常用），1 表示小于，-1 表示大于

例如，若 A1:A5 包含 {Apple, Banana, Cherry, Date, Fig}，则 =MATCH("Cherry", A1:A5, 0) 返回 3，因为 Cherry 是第三项。

组合使用 INDEX 和 MATCH

将 MATCH 嵌套在 INDEX 内部时，真正的威力才会显现。你无需手动输入行号，而是让 MATCH 动态计算：

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

这相当于告诉 Excel："在查找区域中找到我的查找值的位置，然后从返回区域中返回对应的值。"两个区域的大小必须相同，且方向一致。

实操演练

假设有一张产品库存表，结构如下：

产品 ID	产品名称	类别	单价	库存
P-101	无线鼠标	电子产品	$29.99	142
P-102	USB-C 集线器	电子产品	$49.99	87
P-103	台灯	办公	$34.99	55
P-104	A5 笔记本	文具	$8.99	310
P-105	人体工学椅	家具	$299.00	12

数据位于 A2:E6，第 1 行为标题。你希望查找在单元格 H2 中输入 ID 的产品的单价。

使用 INDEX MATCH，H3 中的公式为：

=INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0))

分步说明：

1. MATCH 在 A 列（产品 ID）中搜索 H2 中的值。
2. 返回行位置——例如，若 H2 包含"P-103"，则返回 3。
3. INDEX 随后返回 D 列（单价）第 3 行的值，即 $34.99。

请注意使用带美元符号的绝对单元格引用。锁定区域可确保将公式复制到其他单元格时仍能正确运行。

为什么 INDEX MATCH 优于 VLOOKUP

如果你已经通过我们的 VLOOKUP 完整指南了解了 VLOOKUP，你知道它的优势所在。但它也有众所周知的局限性，而 INDEX MATCH 能够完美解决这些问题。

1. 向左查找——VLOOKUP 不行，INDEX MATCH 可以

VLOOKUP 只能搜索表格最左列并向右返回值。如果你的查找列位于返回列的右侧，VLOOKUP 就会失效。INDEX MATCH 没有这种限制——返回区域和查找区域完全独立，因此你可以从任意列返回值，包括搜索列左侧的列。

2. 列号变动不会破坏公式

VLOOKUP 使用硬编码的列索引号（例如第三列）。插入或删除列后，该编号就会出错，悄悄返回错误数据。由于 INDEX MATCH 引用的是实际区域，插入列永远不会破坏公式。

3. 处理大型数据集速度更快

VLOOKUP 每次计算时都会扫描整个表格数组。INDEX MATCH 只评估特定的查找列和返回列，在包含数万行的工作簿中速度明显更快。

4. 双向（二维）查找

你可以嵌套两个 MATCH 函数——一个用于行，一个用于列——创建 VLOOKUP 无法在不借助辅助公式的情况下实现的二维查找：

=INDEX(B2:E6, MATCH(H2, A2:A6, 0), MATCH(H3, B1:E1, 0))

其中，MATCH(H2, A2:A6, 0) 定位正确的行，MATCH(H3, B1:E1, 0) 定位正确的列。更改任一输入单元格，公式即刻自动适应。这在需要跨多个维度提取指标的销售仪表板中尤为实用。

优雅处理错误

找不到匹配项时，MATCH 会返回 #N/A 错误。将整个 INDEX MATCH 包裹在 IFERROR 中，以显示友好的提示信息：

=IFERROR(INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0)), "未找到产品")

这在共享工作簿或模板中尤为重要，因为最终用户会在其中输入搜索值——简洁的错误处理能防止混乱和困惑。将此与输入单元格上的数据验证结合使用，将输入限制为有效列表，便可打造出健壮、防误操作的查找工具。

INDEX MATCH 多条件查找

最常见的查找需求之一是按多个条件进行匹配。假设你希望查找类别为"Electronics"且库存小于 100 的产品单价，可以使用数组版本的 INDEX MATCH 来实现。

=INDEX($D$2:$D$6, MATCH(1, ($C$2:$C$6="Electronics")*($E$2:$E$6<100), 0))

在旧版 Excel（365 之前），按 Ctrl + Shift + Enter 以数组公式形式输入——Excel 会用大括号 {} 将其括起来。在 Excel 365 和 Excel 2021 中，动态数组会自动处理，直接按 Enter 即可。

原理说明：每个条件生成一个 TRUE/FALSE 值数组（即 1 和 0）。将它们相乘后，得到一个仅在两个条件均为 TRUE 时才为 1 的新数组。MATCH 找到第一个 1，INDEX 返回对应的价格。

INDEX MATCH 与 XLOOKUP

Excel 365 引入了 XLOOKUP，用单个函数简化了许多查找任务。XLOOKUP 非常适合直观的查找场景，且原生支持向左查找。然而，INDEX MATCH 在以下几方面仍然不可或缺：

• 它适用于所有 Excel 版本，包括 Excel 2010、2013、2016 和 2019——无论你在哪里共享工作簿。
• 嵌套 MATCH 函数的二维查找模式比 XLOOKUP 替代方案更简洁。
• 许多专业财务模型和审计模板都基于 INDEX MATCH 构建，因此阅读和维护这些文件需要熟悉该组合。

掌握 INDEX MATCH 也是处理更高级任务的基础，例如在 Excel 中创建动态仪表板——其中查找公式为图表和汇总表提供数据，并自动更新。

INDEX MATCH 成功使用技巧

• 始终使用 0 作为 match_type 进行精确匹配，除非你有特定理由使用近似匹配。
• 用绝对引用（$）锁定区域，确保公式在跨行或跨列复制时仍能正确运行。
• 保持查找区域与返回区域长度一致——大小不匹配会导致错误结果。
• 使用名称框（公式栏左侧）为区域命名，提升可读性：=INDEX(UnitPrices, MATCH(H2, ProductIDs, 0)) 远比单元格引用更易于审核。
• 与 IF 和 SUMIFS 结合使用实现条件查找——相关条件公式技巧请参阅我们的 SUMIF 和 SUMIFS 指南。

即时获取 INDEX MATCH 公式

如果你面对复杂的查找需求——多个条件、非标准表格布局或跨工作表引用——可以用中文向 GPTExcel 描述你的需求，几秒钟内即可获得包含正确绝对引用和错误处理的即用型 INDEX MATCH 公式。它省去了猜测的过程，让你无需手动反复试错就能得到可用的公式。

有关 AI 驱动的更广泛公式编写技巧，请参阅使用 ChatGPT 编写 Excel 公式一文，其中详细介绍了完整工作流程。

常见问题

INDEX MATCH 是否总是优于 VLOOKUP？

对于绝大多数专业使用场景，是的。INDEX MATCH 支持向左查找，不会因插入列而出错，并且支持二维和多条件匹配。VLOOKUP 在编写基本向右查找公式时更简单，但随着数据复杂度的增加，其局限性会愈发明显。

使用 INDEX MATCH 时是否需要按 Ctrl+Shift+Enter？

仅当你在 Excel 2019 或更早版本中使用多条件数组版本公式时才需要。标准单条件 INDEX MATCH 公式在所有 Excel 版本中均可直接按 Enter 键输入。在支持动态数组的 Excel 365 和 Excel 2021 中，即使是多条件版本也不需要使用数组快捷键。

如果查找列中存在重复值会怎样？

MATCH 始终返回找到的第一个匹配项的位置。如果你的查找列存在重复值，且需要检索每次出现的数据，可以考虑使用拼接键的辅助列，或使用 Power Query——请参阅我们的 Power Query 指南——在应用查找前先对数据进行重塑。

INDEX MATCH 能跨不同工作表查找值吗？

可以。只需在区域引用中加入工作表名称即可。例如：=INDEX(Sheet2!$D$2:$D$100, MATCH(H2, Sheet2!$A$2:$A$100, 0))。无论区域位于同一工作表还是同一工作簿中的不同工作表，公式的工作方式完全相同。