如何在 Excel 中使用 INDEX MATCH 匹配多个条件

几年前，我做过一次营销活动分析，需要比较多个地区的销售表现。数据分散在多张 Excel 工作表中，我必须把特定产品的销售数字汇总到一个报告里。一开始我试着手动查找并复制数据，但远没有想象中那么容易。只要有一行出错，整份报告就可能崩盘。

这时我发现了 INDEX MATCH()。虽说花了几次尝试才把公式写对，但当我看到它能轻松定位并提取所需的精确数字后，它就成了我的日常工具。只用两个函数，就能抓取分散在各个表格中的所需数据。

在本文中，我将讲解如何使用 INDEX() 和 MATCH() 函数实现同样的效果。Excel 总有新东西可学。 如果您是初学者，我强烈推荐我们的 Introduction to Excel 课程。如果您已经有一定基础，可以试试我们的 Advanced Excel Functions 课程。

INDEX MATCH 温习

INDEX MATCH 是对两个 Excel 函数组合的简称，它们协同完成高级查找。我们也可以写作 INDEX(MATCH())，但本文统一称为 INDEX MATCH。现在，分别来看这两个函数：

INDEX() 函数根据指定区域内的行列位置返回单元格的值。其语法如下：

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

其中：

• array 是要从中检索值的单元格区域。

• row_num 是在该区域中要返回值的行号。

• column_num（可选）是在该区域中要返回值的列号。

MATCH() 函数用于确定某个值在区域内的相对位置。其语法为：

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

其中：

• lookup_value 是您要查找的值。

• lookup_array 是执行查找的区域。

match_type 为可选。 1 （默认）查找小于或等于 lookup_value 的值（数组需按升序排序）。0 查找精确匹配（数组无需排序）。-1 查找大于或等于 lookup_value 的最小值（数组需按降序排序）。

如何将 INDEX() 与 MATCH() 组合

通过在 INDEX() 中嵌套 MATCH()，我们可以创建动态查找。来看一个示例：假设您想查找 David Wilson 在数据集中的位置。与其在 INDEX() 中硬编码行号，不如用 MATCH() 来确定：

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))技术支持

在上述公式中，MATCH("David Wilson", A2:A6, 0) 返回4，即行位置。而 INDEX(C2:C6, 4) 会从区域 C2:C6 的第 4 行取回值，即Seattle。

将 INDEX() 与 MATCH() 组合使用。图片来源：作者。

为了让公式更具动态性，您可以将硬编码的 David Wilson 替换为单元格引用。这样，公式会根据 D4 中的值自动调整：

=INDEX(C2:C6,MATCH(D4,A2:A6,0))技术支持

在 INDEX MATCH 中替换硬编码值。图片来源：作者。

INDEX MATCH 与 VLOOKUP() 对比

既然您已经了解了 INDEX() 和 MATCH() 的各自作用，以及它们组合后为何更灵活，我们再来看看为什么 INDEX MATCH 比 VLOOKUP() 更合适。

• 与要求查找列必须在左侧的 VLOOKUP() 不同，INDEX MATCH 无论目标列位于何处都能返回数据。

• INDEX MATCH 只处理所需的区域；相比之下，VLOOKUP() 会扫描整张表。

• 使用 VLOOKUP() 的公式在插入或删除列时可能失效，因为它依赖静态列索引；而 INDEX MATCH 引用的是动态区域，能在数据结构变动后保持稳定。

• 用 INDEX MATCH 无需手动计算列序号。只需指定查找列和返回列即可。

多条件下的 INDEX MATCH

我经常处理包含重复项的数据集，要在其中查找值非常困难。但现在我使用 INDEX MATCH，因为与其他标准查找公式不同，它能轻松应对这些场景。下面一步步演示我的做法。

为多条件准备数据

首先，建立数据集，并确保按照表格规范清晰组织，每列都有明确的标题。每一行代表一条唯一记录，每一列对应特定属性。

例如，这里有一个示例数据集：

INDEX MATCH 多条件的数据集。图片来源：作者。

编写多条件公式

当数据整理妥当后，就可以编写公式了。INDEX MATCH 通过识别满足多个条件的行，从另一列返回值。方法是在 MATCH() 中组合多个逻辑测试，并将其嵌入 INDEX()。

其基本语法如下：

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}技术支持

其中：

• Return_range 为返回值所在的区域。

• Criteria1、Criteria2、… 为需要满足的条件。

• Range1、Range2、… 为与条件对应的区域。

数据准备就绪后，我们来重点看看两种方法，回答“如何用 INDEX MATCH 匹配多个条件”。

用辅助列处理复杂条件

如果数据集中包含多个条件，可使用辅助列来简化流程。它会把所有条件合并到单列，便于查找。例如，我用相同的数据集创建了一个辅助列，将 名和 薪资两列合并：

=A2&B2技术支持

创建辅助列。图片来源：作者。

这个辅助列让我的 INDEX MATCH 公式更简洁。无需编写包含多条件的复杂数组公式，我只需在公式中引用辅助列即可：

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))技术支持

配合辅助列的 INDEX MATCH。图片来源：作者。

用数组公式组合多个条件

如果您不想使用辅助列，也可以通过数组公式实现相同效果。这样可以在 MATCH() 中直接评估多个条件。举例来说，下面是我如何在 人力资源（HR）部门中查找 Alice 的薪资：

步骤 1： 编写带逻辑条件的 MATCH()：

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)技术支持

在该公式中，1 使得 MATCH() 查找所有条件均为 TRUE 的行。(F4=A2:A11) 检查 F4 是否匹配范围 A2:A11 中任意值；(F5=B2:B11) 检查 F5 是否匹配范围 B2:B11 中任意值。* 运算符相当于 AND 逻辑，确保所有条件同时满足。

步骤 2： 将上述 MATCH() 嵌入 INDEX()：

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))技术支持

步骤 3：完成公式。如果您使用的是旧版 Excel，请按 Ctrl+Shift+Enter 将其确认为数组公式；新版则直接按 Enter。

数组形式的多条件 INDEX MATCH。图片来源：作者。

多条件 INDEX MATCH 的进阶用法

通过 INDEX MATCH 您还能做更多事。来看几种方式：

结合命名区域与动态区域

我会在 Excel 中使用命名区域，给区域起更有意义的名字，比如 results 或 totalSales，而不是用 A1:A10 这样的标准引用。这样做便于在不同工作表间管理公式。

要为单元格区域命名，先选中单元格，按 Ctrl + F3（Windows）或 Cmd + F3（Mac） 打开名称管理器。然后点击 新建，输入名称并点击 确定。

为区域命名。图片来源：作者。

命名区域与动态区域的区别在于：命名区域指向固定的一组单元格，而动态区域会随数据的添加或删除自动调整。

要设置动态区域，先选中单元格。在 公式选项卡中点击 名称管理器，或按 Ctrl + F3 打开 Excel 的 名称管理器，点击 新建。随后会出现 新建名称对话框。在 名称字段中输入想要的名称，然后在 引用位置字段中输入动态区域的公式。

设置动态区域。图片来源：作者。 

下面举个例子：我定义了两个动态区域和一个静态区域：

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

现在，我在 INDEX MATCH 公式中使用这些区域：

=INDEX(total_amount,MATCH(lookup_value,items_list,0))技术支持

可以看到，使用清晰的名称后，公式更易理解。

将动态与命名区域用于 INDEX MATCH。图片来源：作者。

嵌套 INDEX MATCH 实现复杂查找

除了基础用法，您还可以通过嵌套 INDEX MATCH 来处理更复杂的查找。例如，我有一份数据集，展示不同地区按产品类别的销售额。

原始数据集。图片来源：作者。

我想查找 东部（East）的家具（Furniture）销售额。但要做到这一点，我需要同时匹配产品类别（行）和地区（列），这不是基础 INDEX MATCH 能完成的。因此我使用如下嵌套的 INDEX MATCH 公式：

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))技术支持

工作原理如下：INDEX() 会从 B2:D4 区域取值，但它需要行号和列号来确定具体位置。于是第一个 MATCH(D6, A2:A4, 0) 用来确定行号。如果 D6 为 Furniture，它会在 A2:A4 列中查找，并在第二行找到。

接着，MATCH(D7, B1:D1, 0) 确定列号。如果 D7 为 East，它会横向在 B1:D1 中查找，并在第二列找到。

当 INDEX() 获得行号与列号后，就能返回对应的值。本例中， 东部（East）的 家具（Furniture）销售额为450。

使用嵌套 INDEX MATCH。图片来源：作者。

与其手动在行和列中查找，我更倾向于用这个公式，因为它能更精确地处理所有细节。

常见问题与排错技巧

刚开始使用 INDEX MATCH 时，我也遇到过不少问题，我不想您也走同样的弯路。所以我会带您了解最常见的问题，以及如何解决。

处理 INDEX MATCH 公式中的错误

像 #N/A 和 #VALUE! 这样的错误一开始可能很让人挫败，但其实并不难修复。我们来看看如何定位问题原因，以及解决步骤。

当 MATCH() 找不到值时，会出现 #N/A 错误。这通常是因为查找值不在搜索数组中，或数据包含隐藏空格。 例如，我曾在提取 员工姓名时引用了错误的列：

=INDEX(B2:B6,MATCH(E3,C2:C6,0))技术支持

INDEX MATCH 的 #N/A 错误。图片来源：作者。

要修复此类问题，请确认查找值确实存在于数组中，并使用 TRIM() 去除多余空格：

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

INDEX MATCH 的 #N/A 错误已修复。图片来源：作者。

#VALUE! 错误通常出现在公式未设置为数组公式时。例如，如果我在 MATCH() 中包含多个区域，Excel 会将其视为数组公式。但如果未正确设置，就会抛出 #VALUE! 错误。

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))技术支持

INDEX MATCH 的 #VALUE 错误。图片来源：作者。

解决方法是输入公式后按 Ctrl + Shift + Enter 。这样 Excel 会自动在公式外包上花括号 {}，表示已成为数组公式。不要手动输入花括号，否则会破坏公式。

INDEX MATCH 的 #VALUE 错误已修复。图片来源：作者。

在大型数据集上优化性能

在较大的数据集中，我的公式有时会变慢，导致需要等待计算更新。如果您也有类似困扰，可以尝试以下方法：

• 限制查找范围：仅引用必要的区域。例如，不要用 A:A，而是用 A1:A100 来降低计算量。

• 使用辅助列：用辅助列预先计算复杂条件，减少数组公式的计算负担。

• 启用手动计算模式：将 Excel 切换为手动计算，避免频繁重算。修改完成后按 F9 手动更新公式。

• 避免易变函数：尽量少将 NOW()、RAND()、TODAY() 等易变函数与 INDEX MATCH 搭配使用，它们会在工作簿更新时触发重新计算。

结语

INDEX MATCH 技巧能节省时间、简化复杂的数据分析。若您常处理大规模数据，值得一试。不过，巩固理解的最佳方式仍是实践。建议您动手处理几个数据集，尝试本文提到的方法——这是我提升技能的方式。

如需进一步学习，请查看我们的 Advanced Excel Functions 课程，掌握更广泛的强大工具。如果您想系统提升 Excel 数据分析能力，推荐我们的 Data Analysis in Excel 课程，涵盖从数据准备到可视化的完整流程。