跳至主要内容

立即探索 Kutools for Outlook 全新的免费版本！

Excel INDEX MATCH：基本和高级查找

Author: Amanda Li Last Modified: 2025-05-07

在 Excel 中，准确检索特定数据通常是一个常见的需求。虽然 INDEX 和 MATCH 函数各有其优势，但将它们结合起来可以解锁一个强大的数据查找工具集。它们一起提供了一系列搜索功能，从基本的水平和垂直查找到更高级的功能，如双向、区分大小写和多条件搜索。与 VLOOKUP 相比，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 存在）指的是数组的行号。
  • column_num（可选，但如果省略 row_num 则必需）指的是数组的列号。

例如，要知道列表中第6 个学生 Jeff 的分数，您可以这样使用 INDEX 函数：

=INDEX(C2:C11,6)

A screenshot of the result of the INDEX formula returning the score of the 6th student

√ 注意：范围 C2:C11 是分数列出的地方，而数字6 找到第6 个学生的考试分数。

这里让我们做一个小测试。对于公式 =INDEX(A1:C1,2)，它将返回什么值？--- 是的，它将返回出生日期，即给定行中的第2个值。

现在我们应该知道 INDEX 函数可以完美地与水平或垂直区域一起工作。但如果我们需要它在具有多行和多列的更大范围内返回一个值呢？在这种情况下，我们应该同时应用行号和列号。例如，要在表格范围内而不是单列中找到 Jeff 的分数，我们可以通过 A2 到 C11 的单元格中用行号6 和列号3 定位他的分数，如下所示：

=INDEX(A2:C11,6,3)

A screenshot of the result of the INDEX formula returning Jeff's score from a table range

我们应该了解的有关 Excel 中 INDEX 函数的事项：
  • INDEX 函数可以与垂直和水平区域一起工作。
  • 如果同时使用 row_num 和 column_num 参数，row_num先于 column_num，INDEX 检索指定 row_num 和 column_num交叉处的值。

然而，对于一个具有多行和多列的非常大的数据库，应用精确的行号和列号的公式对我们来说肯定不方便。这时我们应该结合使用 MATCH 函数。

如何在 Excel 中使用 MATCH 函数

Excel 中的 MATCH 函数返回一个数值，即给定范围中特定项目的位置。MATCH 函数的语法如下：

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value（必需）指的是要在 lookup_array 中匹配的值。
  • lookup_array（必需）指的是您希望 MATCH 搜索的单元格范围。
  • match_type （可选）： 1, 0 或 -1.
    • 1（默认），MATCH 将找到小于或等于 lookup_value 的最大值。lookup_array 中的值必须按升序排列。
    • 0，MATCH 将找到第一个完全等于 lookup_value 的值。lookup_array 中的值可以是任何顺序。（对于匹配类型设置为0 的情况，您可以使用通配符。）
    • -1，MATCH 将找到大于或等于 lookup_value 的最小值。lookup_array 中的值必须按降序排列。

例如，要知道 Vera 在名称列表中的位置，您可以这样使用 MATCH公式：

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

A screenshot showing the result of the MATCH formula returning the position of Vera in the list

√ 注意：结果“4”表示名称“Vera”在列表中的第4 个位置。

我们应该了解的有关 Excel 中 MATCH 函数的事项：
  • MATCH 函数返回查找值在查找数组中的位置，而不是值本身。
  • MATCH 函数在重复项的情况下返回第一个匹配项。
  • 就像 INDEX 函数一样，MATCH 函数也可以与垂直和水平区域一起工作。
  • MATCH 不区分大小写。
  • 如果 MATCH公式的 lookup_value 是文本形式，请将其括在引号中。
  • 如果在 lookup_array 中找不到 lookup_value，则返回 #N/A 错误。

现在我们了解了 Excel 中 INDEX 和 MATCH 函数的基本用法，让我们卷起袖子准备结合这两个函数。

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

请参见下面的示例，了解我们如何结合使用 INDEX 和 MATCH 函数：

要找到 Evelyn 的分数，知道考试分数在第3 列，我们可以使用 MATCH 函数自动确定行位置，而无需手动计算。随后，我们可以使用 INDEX 函数检索识别的行和第3 列交叉处的值：

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

A screenshot showing the formula and the result for Evelyn's score

好吧，由于公式可能看起来有点复杂，让我们逐一了解每个部分。

A screenshot showing the breakdown of the formula for combining INDEX and MATCH to find Evelyn's score

INDEX公式包含三个参数：

  • row_num: MATCH("Evelyn",A2:A11,0)为 INDEX 提供范围 A2:A11 中值 "Evelyn" 的行位置，即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)

（广告）使用 Kutools 简化查找：无需输入公式！

Kutools for Excel 的 Super Lookup 提供多种查找工具，满足您的各种需求。无论您是在执行多条件查找、跨多个工作表搜索，还是进行一对多查找，Super Lookup 都能通过简单的几次点击简化流程。探索这些功能，看看 Super Lookup 如何改变您与 Excel 数据的交互方式。告别记住复杂公式的麻烦。

A screenshot of Kutools for Excel's Super Lookup tools in Excel ribbon

Kutools for Excel - 通过超过300个必备工具，让Excel功能大幅提升。永久免费享受AI功能！立即获取

INDEX 和 MATCH公式示例

在这一部分，我们将讨论使用 INDEX 和 MATCH 函数以满足不同需求的不同情况。

INDEX 和 MATCH 应用于双向查找

在前面的示例中，我们知道列号并使用 MATCH公式找到行号。但如果我们也不确定列号呢？

在这种情况下，我们可以通过使用两个 MATCH 函数执行双向查找，也称为矩阵查找：一个用于查找行号，另一个用于确定列号。例如，要知道 Evelyn 的分数，我们应该使用公式：

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

A screenshot showing a two-way lookup using INDEX and MATCH in Excel to find Evelyn's score

此公式的工作原理：
  • 第一个 MATCH公式在列表 A2:A11 中找到 Evelyn 的位置，为 INDEX 提供5作为行号。
  • 第二个 MATCH公式确定分数的列，并返回3作为 INDEX 的列号。
  • 公式简化为 =INDEX(A2:C11,5,3)，INDEX 返回90。
INDEX 和 MATCH 应用于左侧查找

现在，让我们考虑一个需要确定 Evelyn 班级的情况。您可能注意到班级列位于名称列的左侧，这种情况超出了另一个强大的 Excel 查找函数 VLOOKUP 的能力。

事实上，执行左侧查找的能力是 INDEX 和 MATCH组合优于 VLOOKUP 的方面之一。

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

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

A screenshot showing how to use INDEX and MATCH to find Evelyn's class from a left-side lookup in Excel

注意：您可以轻松地使用 Kutools for Excel 的从右到左查找功能，通过简单的几次点击执行特定值的左侧查找。要实现此功能，请导航到 Excel 中的Kutools选项卡，然后在公式组中单击Super Lookup > 从右到左查找。

A screenshot of the LOOKUP from Right to Left feature in Kutools for Excel

Kutools for Excel - 通过超过300个必备工具，让Excel功能大幅提升。永久免费享受AI功能！立即获取

INDEX 和 MATCH 应用于区分大小写查找

MATCH 函数本质上不区分大小写。然而，当您需要公式区分大小写字符时，可以通过结合 EXACT 函数来增强它。通过在 INDEX公式中将 MATCH 函数与 EXACT结合，您可以有效地执行区分大小写的查找，如下所示：

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array),0))
  • array 指的是您希望返回值的区域。
  • lookup_value 指的是要匹配的值，考虑字符的大小写，在 lookup_array 中。
  • lookup_array 指的是您希望 MATCH 与 lookup_value 比较的单元格范围。

例如，要知道 JIMMY 的考试分数，请使用以下公式：

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

√ 注意：这是一个数组公式，您需要按 Ctrl + Shift + Enter 输入，除非在 Excel365、Excel2021 和更新版本中。

A screenshot showing how to use INDEX and MATCH with EXACT for a case-sensitive lookup in Excel

此公式的工作原理：
  • EXACT 函数将 "JIMMY" 与列表 A2:A11 中的值进行比较，考虑字符的大小写：如果两个字符串精确匹配，包括大小写字符，EXACT 返回 TRUE；否则返回 FALSE。结果，我们获得一个包含 TRUE 和 FALSE 值的数组。
  • MATCH 函数然后检索数组中第一个 TRUE 值的位置，应该是10。
  • 最后，INDEX 检索 MATCH 在数组中提供的第10 个位置的值。

注意：

  • 记得正确输入公式，按 Ctrl + Shift + Enter，除非您使用的是 Excel365、Excel2021 或 更新版本，在这种情况下，只需按 Enter。
  • 上述公式在单列表 C2:C11 中搜索。如果您想在具有多列和多行的范围内搜索，例如 A2:C11，您应该为 INDEX 提供列号和行号：
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • 在这个修订后的公式中，我们使用 MATCH 函数在范围 A2:A11 中搜索 "JIMMY"，考虑字符的大小写，一旦找到匹配项，我们从范围 A2:C11 的第3 列中检索相应的值。
INDEX 和 MATCH 查找最接近匹配

在 Excel 中，您可能会遇到需要在数据集中找到与特定值最接近或最近的匹配项的情况。在这种情况下，使用 INDEX 和 MATCH 函数的组合，以及 ABS 和 MIN 函数，可以非常有帮助。

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • 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 输入，除非在 Excel365、Excel2021 和更新版本中。

A screenshot demonstrating how to use INDEX and MATCH with ABS and MIN functions to find the closest match in Excel

此公式的工作原理：
  • ABS(C2:C11-85)计算范围 C2:C11 中每个值与85之间的绝对差，结果是一个绝对差的数组。
  • MIN(ABS(C2:C11-85)) 找到绝对差数组中的最小值，代表与85 的最接近差异。
  • MATCH 函数 MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) 然后找到绝对差数组中最小绝对差的位置，应该是10。
  • 最后，INDEX 检索列表 A2:A11 中对应于范围 C2:C11 中最接近85 的分数的位置的值。

注意：

  • 记得正确输入公式，按 Ctrl + Shift + Enter，除非您使用的是 Excel365、Excel2021 或 更新版本，在这种情况下，只需按 Enter。
  • 在平局的情况下，此公式将返回第一个匹配项。
  • 要找到与平均分数最接近的匹配项，请将公式中的85 替换为 AVERAGE(C2:C11)。
INDEX 和 MATCH 应用于多条件查找

要找到符合多个条件的值，需要您跨两个或更多列进行搜索，请使用以下公式。该公式允许您通过在不同列中指定各种条件来执行多条件查找，帮助您找到符合所有指定条件的所需值。

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…),0))

√ 注意：这是一个数组公式，您需要按 Ctrl + Shift + Enter 输入。然后在公式栏中会出现一对大括号。

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

例如，要找到班级 A 的 Coco 的分数，其出生日期为2008 年7 月2 日，您可以使用以下公式：

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

A screenshot demonstrating the use of INDEX and MATCH for multi-criteria lookup in Excel

注意：

  • 在此公式中，我们避免硬编码值，通过修改单元格 G2、G3 和 G4 中的值，可以轻松获取不同信息的分数。
  • 您应该按 Ctrl + Shift + Enter 输入公式，除非在 Excel365, Excel2021 或 更新版本中，在这种情况下，只需按 Enter.
    如果您经常忘记使用 Ctrl + Shift + Enter 来完成公式并得到错误结果，请使用以下稍微复杂的公式，您可以通过简单的 Enter 键完成： 
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • 公式可能复杂且难以记住。为了简化多条件查找而无需手动输入公式，请考虑使用 Kutools for Excel的 多条件查找 功能。一旦安装了 Kutools，请导航到 Kutools 选项卡，在 Excel 中单击 Super Lookup > 多条件查找 在 公式 组中。

    A screenshot of the Multi-condition Lookup feature in Kutools for Excel

    Kutools for Excel - 通过超过300个必备工具，让Excel功能大幅提升。永久免费享受AI功能！立即获取

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)), "")

A screenshot of the formula used to apply a lookup across multiple columns

这就是 Kutools for Excel 的 索引并匹配多列 功能的用武之地。它简化了流程，使得匹配特定条目与其对应类别变得快速而简单。要解锁这个强大的工具并轻松地将 Shawn 与他的班级匹配，只需 下载并安装 Kutools for Excel 插件，然后按以下步骤操作：

  1. 选择您希望显示匹配班级的目标单元格。
  2. 在 Kutools选项卡上，单击公式助手 > 查找与引用 > 索引并匹配多列。
  3. A screenshot of the Index and Match on Multiple Columns option on the Kutools tab in Excel
  4. 在弹出的对话框中，按以下步骤操作：
    1. 单击第1 个 A screenshot of the range selection button in the Fomrulas Helper dialog 按钮旁边 查找列 选择包含您要返回的关键信息的列，即班级名称。（此处只能选择单列。）
    2. 单击第2 个 A screenshot of the range selection button in the Fomrulas Helper dialog 按钮旁边 表格区域 选择单元格以匹配所选 查找列中的值，即学生姓名。
    3. 单击第3 个 A screenshot of the range selection button in the Fomrulas Helper dialog 按钮旁边 查找值 选择包含您要与其班级匹配的学生姓名的单元格，在本例中为 Shawn。
    4. 单击 OK。
    5. A screenshot of the Fomrulas Helper dialog

结果

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

A screenshot of the formula generated by Kutools locating Shawn's class name from a table

注意：要尝试索引并匹配多列功能，您需要在计算机上安装 Kutools for Excel。如果您尚未安装，请不要等待 ---立即下载并安装。让 Excel 更智能地工作吧！

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))

    A screenshot of the INDEX MATCH formulas used to lookup for first non-blank value

注意：

  • 以上是数组公式，您需要按 Ctrl + Shift + Enter 输入，除非在 Excel365、Excel2021 和更新版本中。
  • 查看此教程以获取详细说明：获取列或行中的第一个非空值。
INDEX 和 MATCH 查找第一个数字值

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

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

A screenshot of the INDEX MATCH formulas used to lookup for the first numeric value

注意：

  • 这是一个数组公式，您需要按 Ctrl + Shift + Enter 输入，除非在 Excel365、Excel2021 和更新版本中。
  • 查看此教程以获取详细说明：获取列或行中的第一个数字值。
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))

A screenshot of the INDEX MATCH formula used to lookup for MAX associations

此公式的工作原理：
  • MAX(C2:C11) 搜索范围 C2:C11 中的最高值，即96。
  • MATCH 函数然后找到数组 C2:C11 中最高值的位置，应该是1。
  • 最后，INDEX 检索列表 A2:A11 中的第1 个值。

注意：

  • 在存在多个最大或最小值的情况下，如上例中两个学生获得相同的最高分数，此公式将返回第一个匹配项。
  • 要确定谁的分数最低，请使用以下公式： 
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))
提示：定制您的 #N/A 错误消息

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

A screenshot of the #N/A error result returned by an INDEX MATCH formula

为了使您的电子表格更具用户友好性，您可以通过将您的 INDEX MATCH公式包装在 IFNA 函数中来自定义此错误消息：

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

A screenshot of the #N/A error replaced with a tailored message using INDEX and MATCH

注意：

  • 您可以通过将“未找到”替换为您选择的任何文本来自定义错误消息。
  • 如果您想处理所有错误，而不仅仅是 #N/A，请考虑使用 IFERROR 函数而不是 IFNA: 
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    请注意，抑制所有错误可能不是明智之举，因为它们可以作为您公式中潜在问题的警报。

以上是所有与 Excel 中 INDEX 和 MATCH 函数相关的内容。希望您觉得本教程有帮助。如果您想探索更多 Excel 技巧和窍门，请点击此处访问我们丰富的数千个教程集合。