Excel INDEX MATCH：基础与高级查找

在 Excel 中，精准检索特定数据是一项常见且关键的需求。虽然 INDEX 和 MATCH 函数各自优势突出，但将二者强强联合，即可解锁一套功能强大的数据查找利器。它们不仅支持基础的横向与纵向查找，更能轻松实现双向查找、区分大小写的精确匹配，以及多条件联合查询等高级功能。相比 VLOOKUP，INDEX 与 MATCH 的组合提供了更灵活、更全面的数据查找解决方案。在本教程中，我们将深入探索这对黄金搭档所能释放的无限潜能。

• INDEX 函数
• MATCH 函数
• 组合使用 INDEX 和 MATCH

• 双向查找
• 向左查找
• 区分大小写的查找
• 最接近匹配
• 多条件查找
• 跨多列查找
• 查找第一个非空值
• 查找第一个数值
• 查找最大值或最小值的关联项
• 提示：自定义您的#N/A 错误消息

如何在 Excel 中使用 INDEX 和 MATCH

在使用 INDEX 和 MATCH 函数之前，先来了解它们如何助您精准查找所需数值。

如何在 Excel 中使用 INDEX 函数

Excel 中的 INDEX 函数可返回指定范围内特定位置的值。其语法如下：

• array（必填）指您希望从中返回值的区域。
• row_num（必填，除非已提供 ）column_num）指数组中的行号。
• column_num（可选，但如果省略了 ）row_num，则必须提供）指数组中的列号。

例如，若要查询 Jeff 的分数，即列表中第 6 名学生的成绩，可以这样使用 INDEX 函数：

=INDEX(C2:C11,6)

√ 注意：范围 C2:C11 为分数所在区域，数字 6 用于查找第 6 名学生的考试成绩。

现在我们来做个小测试。对于公式 =INDEX(A1:C1,2)，它会返回什么值？——没错，它将返回该行中第 2 个值，即出生日期。

现在我们已经知道，INDEX 函数可完美适用于横向或纵向范围。但如果需要在包含多行多列的更大范围内返回某个值，又该如何操作呢？此时，需同时指定行号和列号。例如，若要在整个表格区域（而非单列）中查找 Jeff 的分数，即可在 A2 到 C11 单元格区域 中，定位到 第 6 行 与 第 3 列 交叉处的分数，如下所示：

=INDEX(A2:C11,6,3)

然而，面对包含大量行列的真实数据库，手动输入确切的行号和列号显然不够便捷。这时，我们就需要结合使用 MATCH 函数。

如何在 Excel 中使用 MATCH 函数

Excel 中的 MATCH 函数返回指定项在给定范围内的位置，结果为一个数值。其语法如下：

• lookup_value（必填）是指要在 lookup_array 中进行匹配的值。
• lookup_array（必填）是指您希望 MATCH 函数在其中搜索的单元格区域。
• match_type（可选）：1、0 或 -1.
  • 1（默认）：MATCH 将查找小于或等于 lookup_value 的最大值，且 lookup_array 中的值必须按升序排列。
  • 0时，MATCH 函数将查找 lookup_value 在 lookup_array 中第一个完全相等的值。lookup_array 中的值可为任意顺序。（当匹配类型设为 0 时，支持使用通配符。）
  • -1时，MATCH 函数将查找大于或等于 lookup_value 的最小值，且 lookup_array 中的值必须按降序排列。

例如，若要查询 名称列表中 Vera 的位置，可以这样使用区分公式：

=MATCH("Vera",A2:A11,0)

√ 注意：结果“4”表示姓名“Vera”位于列表的第 4 位。

现在我们已经了解了 Excel 中 INDEX 和 MATCH 函数的基本用法，接下来就动手将这两个函数结合起来使用吧。

如何在 Excel 中组合使用 INDEX 和 MATCH

请参见以下示例，了解如何组合使用 INDEX 和 MATCH 函数：

要查找 Evelyn 的分数，且已知考试成绩位于第 3 列，可 使用 MATCH 函数自动确定行位置，无需手动计数；随后，再通过 INDEX 函数获取 该行与第 3 列交叉处的值：

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

由于该公式可能略显复杂，下面我们逐部分进行解析。

该 INDEX 公式包含三个参数：

• row_num：MATCH("Evelyn",A2:A11,0)向 INDEX 提供“Evelyn”在区域 A2:A11 中的行位置，即5。
• column_num：3 指定 INDEX 在数组中定位分数所用的第 3 列。
• array：A2:C11 指示 INDEX 函数在指定行与列的交叉处返回匹配值，该范围从 A2 到 C11. 最终，我们得到结果 90.

在上述公式中，我们使用了一个硬编码值 “Evelyn”。但在实际应用中，硬编码值并不实用——每次查询不同数据（例如另一位学生的分数）时，都需手动修改公式。此时，我们可以改用单元格引用，创建动态公式。例如，在本例中，我将 “Evelyn”替换为 F2：

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

INDEX 和区分公式示例

本节将介绍在不同场景下如何使用 INDEX 和 MATCH 函数以满足各种需求。

使用 INDEX 和 MATCH 实现双向查找

在前面的示例中，我们已知列号，并通过区分公式查找行号。但如果连列号也不确定呢？

在这种情况下，可通过两个 MATCH 函数实现双向查找（也称为矩阵查找）：一个用于确定行号，另一个用于确定列号。例如，若要查询 Evelyn 的分数，请使用以下公式：

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

使用 INDEX 和 MATCH 实现向左查找

现在，假设您需要确定 Evelyn 所在的班级。您可能注意到，“班级”列位于“姓名”列的左侧，这种情况超出了另一强大 Excel 查找函数 VLOOKUP 的能力范围。

事实上，能够向左查找正是 INDEX 与 MATCH 组合优于 VLOOKUP 的一大优势。

要查找 Evelyn 的班级，请使用以下公式：在 B2:B11 中搜索 Evelyn，并 从 A2:A11 中检索对应的值。

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

使用 INDEX 和 MATCH 实现区分大小写的查找

MATCH 函数本身不区分大小写。但当您需要公式区分大小写字母时，可结合 EXACT 函数来增强功能。将 MATCH 与 EXACT 融入 INDEX 公式中，即可轻松实现区分大小写的精准查找，如下所示：

• array 指您希望从中返回值的区域。
• lookup_value 是指要在 lookup_array 中进行匹配的值（区分大小写）。
• lookup_array 是指您希望 MATCH 用来与 lookup_value 进行比较的单元格区域。

例如，若要查询 JIMMY 的考试成绩，请使用以下公式：

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ 注意：这是一个数组公式，需按 Ctrl+Shift+Enter 输入（Excel 365、Excel 2021 及更新版本除外）。

使用 INDEX 和 MATCH 查找最接近的匹配项

在 Excel 中，您可能会遇到需要在数据集中查找最接近某个特定值的情况。此时，结合使用 INDEX 和 MATCH 函数，并辅以 ABS 和 MIN 函数，将非常有帮助。

• array 指您希望从中返回值的区域。
• lookup_array 是指您希望在其中查找与 lookup_value 最接近匹配项的值区域。
• lookup_value 是指要查找其最接近匹配项的值。

例如，若要找出 谁的分数最接近 85，请使用以下公式：在 C2:C11 中搜索最接近 85 的分数，并 从 A2:A11 中检索对应的值。

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ 注意：这是一个数组公式，需按 Ctrl+Shift+Enter 输入，Excel 365、Excel 2021 及更新版本除外。

使用 INDEX 和 MATCH 实现多条件查找

若要查找满足多个条件的值（即需跨两列或多列进行搜索），请使用以下公式。该公式允许您通过在不同列中指定多个条件，执行多条件查找，从而找到满足所有指定条件的目标值。

√ 注意：这是一个数组公式，需按 Ctrl+Shift+Enter 输入，此时编辑栏中将显示一对花括号。

• array 指您希望从中返回值的区域。
• (lookup_value=lookup_array)表示一个单一条件，用于检查特定的 lookup_value 是否与 lookup_array 中的值匹配。

例如，若要查找 出生日期为 7/2/2008 的 A 班学生 Coco 的分数，可使用以下公式：

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

使用 INDEX 和 MATCH 实现跨多列查找

设想一种场景：您正在处理多个数据列，其中第一列为键值，用于对其他列中的数据进行分类。要确定某条记录所属的类别或分类，您需要在数据列中进行搜索，并将其与参考列中的相应键值关联起来。

例如，在下表中，我们如何使用 INDEX 和 MATCH 函数将学生 Shawn 与其对应的班级匹配？虽然可以通过公式实现，但该公式相当冗长，不仅难以理解，更不用说记忆和输入了。

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

这时，Kutools for Excel 的索引并匹配多列功能就派上用场了！它能简化整个流程，让您快速、轻松地将特定条目与其对应类别精准匹配。要立即解锁这一强大工具，并轻松将 Shawn 与其班级匹配，请 下载并安装 Kutools for Excel 加载项，然后按以下步骤操作：

1. 请选择用于显示匹配班级的目标单元格。
2. 在 Kutools 选项卡中，单击公式助手 > 查找和引用 > 索引并匹配多列。
3. 
3. 在弹出的对话框中，按如下操作：
   1. 单击 Lookup_col 旁的 1st 按钮，选择包含要返回的关键信息（即班级名称）的列。（此处仅可选择单列。）
   2. 单击 Table_rng 旁的 2nd 按钮（位于此处），选择用于匹配所选 Lookup_col 中值（即学生姓名）的单元格。
   3. 单击位于 Lookup_value 旁的 3rd 按钮，选择包含要匹配其班级的学生姓名（本例中为 Shawn）的单元格。
   4. 单击确定。
   5. 

结果

Kutools 已自动生成公式，您会立即在目标单元格中看到 Shawn 的班级名称。

使用 INDEX 和 MATCH 查找首个非空值

若要从一列或一行中提取首个非空值（忽略错误值），可使用基于 INDEX 和 MATCH 函数的公式；若您不希望忽略范围内的错误值，则需结合 ISBLANK 函数。

• 忽略错误，获取列或行中的第一个非空值：

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• 包含错误，获取列或行中的第一个非空值：

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

使用 INDEX 和 MATCH 查找首个数值

若要从一列或一行中检索首个数值，请使用基于 INDEX、MATCH 和 ISNUMBER 函数的公式。

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

使用 INDEX 和 MATCH 查找最大值或最小值对应的关联项

如果需要检索与范围内最大值或最小值相关联的值，可以结合使用 MAX 或 MIN 函数以及 INDEX 和 MATCH 函数。

• 使用 INDEX 和 MATCH 检索与最大值关联的值：
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• 使用 INDEX 和 MATCH 检索与最小值关联的值：
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• 上述公式包含两个参数：
• • array 指您希望从中返回相关信息的数据区域。
  • lookup_array 表示用于检查或搜索特定条件（例如最大值或最小值）的值集合。

例如，如果您想确定谁的分数最高，请使用以下公式：

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

提示：自定义您的#N/A 错误消息

使用 Excel 的 INDEX 和 MATCH 函数时，若无匹配结果，可能会遇到 #N/A 错误。例如，在下表中，当尝试查找名为 Samantha 的学生的分数时，由于她不在数据集中，因此会出现 #N/A 错误。

为提升电子表格的用户友好性，您可以通过 IFNA 函数包裹您的 INDEX 区分公式来自定义此错误提示信息：

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

以上是 Excel 中 INDEX 和 MATCH 函数的全部相关内容，希望本教程对您有所帮助！如需探索更多 Excel 使用技巧，请点击此处，立即访问我们涵盖数千篇实用教程的丰富资源库，轻松提升工作效率！