Excel 中的 INDEX 和 MATCH 函数

Microsoft Excel，简称 Excel，是最广泛使用的强大电子表格程序之一，它允许用户在多个工作表中的多个单元格中记录大量的财务数据。它还具有广泛的内置函数和公式，可以帮助我们对记录的数据执行财务分析和复杂计算。INDEX 和 MATCH 是两个不同的内置 Excel 函数，通常用于执行高级查找。

本文简要介绍了 INDEX 和 MATCH 函数。此外，它还通过相关示例解释了如何将 INDEX 和 MATCH 函数结合使用以在 Excel 中执行复杂的查找。

Excel 中的 INDEX 函数是什么？

INDEX 函数主要用于高级 Excel 公式中。特别是，INDEX 函数帮助我们检索范围中指定位置的值。换句话说，INDEX 函数通常返回我们指定的行和列交叉点的单元格的内容。但是，需要注意的是，该函数仅在一维范围内返回特定值。

INDEX 函数语法

INDEX 函数的语法定义如下：

其中 reference、row 和 column 是 INDEX 函数的相应参数或参数。

参数/参数

• reference：这是一个必需参数，用于指定需要偏移的单元格数组。引用可以指定为单个范围或特定表中的整个数据集。
• row：这是一个可选参数，用于指定偏移的行数。使用此参数，我们指定要在指定范围内提取值的垂直距离。假设我们将引用范围指定为“A1:A6”。在这种情况下，A1 将使用 1，A2 将使用 2，依此类推。如果输入行数为 5，则函数将从 A5 提取。如果不为此行指定任何值，则函数将提取引用范围中指定的所有行，即 A1 到 A6。
• column：这是另一个可选参数，用于指定偏移的列数。使用此参数，我们指定要在指定范围内提取值的水平距离。假设我们将引用范围指定为“A1:B6”。在这种情况下，对于 A1，行和列都将是 1。但是，对于 B1，行将是 1，但列将是 2。如果输入行数为 5 且列数为 2，则函数将从 B5 提取值。如果不为此列指定任何值，则函数将提取引用范围中指定的​​所有列。

注意：如果不同时指定行和列，INDEX 函数将提取其中提供的整个引用范围。

示例

让我们通过各种用例的示例来理解 INDEX 函数的应用。以我们所有的 INDEX 函数案例为例，请考虑以下 Excel 列表作为示例数据集。在我们的示例表中，第一个单元格从 B2（Item）开始，到最后一个对角线单元格 F9（1797）结束。

情况 1：未指定行和列时

当我们在示例表中在 G2 单元格中插入 INDEX 函数时，如果未指定可选参数行和列，即 **=INDEX(B2:C9)**，INDEX 函数将返回以下结果：

情况 2：指定了引用范围和行

如果我们仅在 INDEX 函数中指定了行和引用范围，但未指定列，我们将得到以下结果：

情况 3：指定了引用范围和列

如果我们仅在 INDEX 函数中指定了列和引用范围，但未指定行，我们将得到以下结果：

情况 4：指定了所有参数

如果在 INDEX 函数中指定了所有参数，如行、列和引用范围，我们将得到以下结果：

INDEX 函数的局限性在于，该函数需要行和列值才能从引用表中定位任何特定数据。这意味着，如果我们使用 INDEX 函数处理包含 10,000 行或更多行的 ML 数据集，我们将很难在 INDEX 函数中指定任何特定的行和/或列值。这时我们就需要使用 MATCH 函数，该函数允许我们根据某些规则或条件识别行和列。

Excel 中的 MATCH 函数是什么？

与 INDEX 函数一样，MATCH 函数的应用也主要在高级 Excel 函数中。特别是，MATCH 函数帮助我们检索范围中指定位置的项目/值的位置。换句话说，该函数帮助我们在给定数组中查找查找值的位置。

MATCH 函数被认为是 Excel 的 VLOOKUP 或 HLOOKUP 函数的简化形式，它只返回位置信息，而不复制或提取实际数据。它不区分大小写，并且与范围的位置无关，无论是垂直还是水平。

MATCH 函数语法

MATCH 函数的语法定义如下：

其中 search_key、range 和 search_type 是 MATCH 函数的相应参数或参数。

参数/参数

• search_key：这是 MATCH 函数必需的参数，用于指定要查找的值。例如，任何特定名称（Akash）、项目（Mouse）、数字（907）、ID（JTP-001）等。
• range：这是 MATCH 函数的另一个必需参数，用于指定要查找的一维数组。它可以表示为单行或单列。例如，A1:D1、A1:A9、C1:C10 等。
• search_type：这是可以在 MATCH 函数中提供的搜索方法。它是一个可选参数。它通常使用以下三个值：1、0、-1。
  • 当 search_type =1 时：这是 MATCH 函数的默认搜索值，如果我们不提供期望的值。在这种情况下，当定义的范围按升序排序时，函数会查找小于或等于 search_key 的最大值。它通常接受近似匹配，向下舍入到下一个可用值。
  • 当 search_type =0 时：在这种情况下，当范围按升序或降序排序时，函数会查找精确值。如果函数找不到精确匹配，则返回错误。
  • 当 search_type = -1 时：在这种情况下，当范围按降序排序时，函数会查找大于或等于 search_key 的最小值。它通常接受近似匹配，向上舍入到下一个可用值。

示例

让我们通过各种用例的示例来理解 MATCH 函数的应用。现在，让我们再次以相同的表作为所有 MATCH 函数用例的示例数据。

情况 1：search_type 为零 (0) 时，表示精确匹配

当我们在示例表中在 G2 单元格中插入 MATCH 函数时，如果我们指定可选参数 search_type 为 0（精确匹配），即 **=MATCH("Tool",C2:C9,0)**，函数将返回以下结果：

情况 2：search_type 为一 (1) 或默认值

如果我们指定 search_type 为 1，这是默认参数值，我们将得到以下结果：

值得注意的是，如果我们不为 search_type 参数指定任何内容，结果将是相同的。相同值的原因是 MATCH 函数默认自动将其视为 1。

情况 3：search_type 为负一 (-1) 时

如果我们指定 search_type 为 -1，我们将得到以下结果：

在大多数情况下，我们通常使用精确匹配，因此我们将 search_type 指定为 0。我们通常借助 MATCH 函数找到行/列号，然后使用 INDEX 函数中的值。这样，如果 INDEX 函数找到有关提供的行/列号的信息（或值），则相应的​​信息会提取到结果单元格中。

将 INDEX 和 MATCH 函数结合起来

我们已经了解了 INDEX 和 MATCH 函数的基础知识。但是，如果我们将这两个函数组合在一个公式中会更有益。当这两个函数结合在一起时，它们就成为执行高级查找最受欢迎的 Excel 工具。由于其有用性，许多用户甚至选择使用组合的 INDEX 和 MATCH 函数而不是 VLOOKUP。

将 INDEX 和 MATCH 函数结合起来通常使我们能够查看数据范围并提取特定行和列交叉点的所需值。嵌套这两个函数非常灵活且高效，我们可以执行**垂直和水平查找、左查找、双向查找、区分大小写的查找、最接近的匹配，甚至可以根据自定义规则或条件进行查找。**

Excel 的 VLOOKUP 函数只能在数据最左侧的列中搜索值，以提供特定的相邻值。但是，我们可以将 INDEX 和 MATCH 函数结合使用来导航任何列并检索任何行的值。

语法

INDEX 和 MATCH 函数组合的语法定义如下：

示例

让我们再次考虑相同的示例表，并将组合公式应用于 INDEX 和 MATCH 函数以查找所需的值。

假设我们要查找 Keyboard 的成本。可以看到，所有项目的成本都记录在 D 列（在我们的范围 B2:F9 中是第 3 列）。如果数据量很大，我们不知道行的位置。因此，让我们假设 Keyboard 行的位置未知。在这种情况下，我们按以下两步应用 INDEX 和 MATCH 的组合公式：

• 首先，我们需要使用以下方法找到 Keyboard 项目的位置，使用 MATCH 函数：
  =MATCH("Keyboard",B2:B9,0)
  在这里，范围 B2:B9 代表包含项目列表的列，并且 search_type 设置为 0 以查找精确匹配。这样，我们就可以找到 Keyboard 项目的确切行号或位置。
• 接下来，我们需要查找 Keyboard 项目的成本。为此，我们需要对整个范围应用 INDEX 函数，并将 MATCH 函数查询嵌套在 INDEX 函数中。已​​经知道记录成本的列号。因此，我们在要计算 Keyboard 成本的单元格 (I3) 中应用组合公式，如下所示：
  =INDEX(B2:F9, MATCH("Keyboard",B2:B9,0),3)
  

让我们探索 INDEX 和 MATCH 函数组合在不同情况下的其他示例：

使用 INDEX 和 MATCH 进行双向查找

在上一个示例中，成本列的位置是已知的。因此，过程不是完全动态的。让我们考虑我们不知道列位置，并且需要查找 Keyboard 项目的成本。在这种情况下，我们必须使用 INDEX 和 MATCH 函数的双向查找。当我们使用 MATCH 函数（不提供任何静态值）同时找到行值和列值，并将其提供给 INDEX 函数以获得特定结果时，这称为双向查找。

在我们的示例中，我们首先需要按以下方式应用 MATCH 公式：

=MATCH("Cost",B2:F2,0)

上面的公式将找到成本的列号。这里，B2:F2 代表标题。

现在，我们将上述公式嵌套在 INDEX 函数中，使其成为双向查找，如下所示：

=INDEX(B2:F9,MATCH("Keyboard",B2:B9, 0),MATCH("Cost" ,B2:F2 ,0))

使用 INDEX 和 MATCH 进行左查找

执行“左查找”是 INDEX 和 MATCH 函数的主要优点之一，而 VLOOKUP 无法做到这一点。它是能够从右侧提取所需项目行的位置，并从左侧查找相应的值。

简单来说，假设我们要查找一个购买成本为 699 卢比的项目。在我们的示例数据中，我们可以看到我们指的是 Keyboard。这里，成本是已知的，必须找到项目名称。

由于成本列位于项目列的右侧，因此使用 VLOOKUP 无法找到所需的结果（项目名称）。现在，让我们使用 INDEX-MATCH 函数查找成本为 699 的项目。

• 首先，我们需要应用以下公式来提取包含我们所需成本值（即 699）的行位置：
  =MATCH(699,D2:D9,0)
  其中 D2:D9 代表成本列中公式将查找所需成本的范围。
• 一旦找到行号，我们就需要在结果单元格 (I2) 中应用 INDEX 函数来提取相应的项目名称。因此，我们将 MATCH 公式嵌套在 INDEX 函数中，如下所示：
  =INDEX(B2:B9,MATCH(699,D2:D9,0))
  其中 B2:B9 代表项目列中公式将查找相应项目（即 Keyboard）的范围。
  

使用 INDEX 和 MATCH 进行区分大小写的查找

默认情况下，Excel 的 MATCH 函数不区分大小写。这意味着当我们使用 MATCH 函数查找项目 Keyboard 的行位置但以“KEYBOARD”、“keyboard”或“KeyBoard”的形式提供术语时，函数将返回相同的结果。因此，当我们将 MATCH 函数与 INDEX 函数结合使用以执行区分大小写的查找时，我们必须使用 EXACT 函数，该函数会区分大小写。

EXACT 函数通常用于比较两个不同的字符串，同时考虑它们的大小写匹配（大写和小写字符），如果它们完全相同，则返回 TRUE。如果它们不完全匹配，EXACT 函数将返回 FALSE。这是因为 EXACT 函数区分大小写。

假设我们要查找我们所需的项目“Keyboard”的类别，但要以严格区分大小写的方式。

我们可以使用 INDEX-MATCH 和 EXACT 函数的以下组合：

=INDEX(C2:C9,MATCH(TRUE,EXACT("Keyboard", B2:B9) ,0))

在上面的公式中，当 MATCH 函数在定义的范围 B2:B9 中找到精确值“Keyboard”时，它返回 TRUE；否则返回 FALSE。之后，MATCH 函数将在范围 B2:B9 中搜索并找到返回 TRUE 的值所在的行位置。最后，INDEX 函数将在 MATCH 函数定位的行处，在 C2:C9 的范围内提取相应的值（项目名称）。这就是 INDEX-MATCH 查找如何以区分大小写的方式工作。

除此之外，如果我们尝试使用大写字符（KEYBOARD）查找项目“Keyboard”的类别，公式将返回 #N/A！错误，因为在 MATCH 函数求值中找不到 TRUE 值。

使用 INDEX 和 MATCH 进行多条件查找

在 Excel 中执行多条件查找有点棘手。在这种情况下，我们必须查找同时匹配多个列的值。我们可以利用 INDEX-MATCH 函数与布尔逻辑结合来同时查找多个列上的匹配项。

假设我们要查找项目“Keyboard”的总成本，其中其单价是“699”，数量总数是“6”。因此，我们必须匹配三个不同的条件，例如项目、单价和数量。我们可以通过以下步骤做到这一点：

• 首先，我们需要使用以下条件匹配项目列 (B2:B9) 和“Keyboard”：
  "Keyboard"=B2:B9
  这样，我们就将范围 B2:B9（即我们的项目列）的值转换为布尔值。当项目是“Keyboard”时，它返回 TRUE，否则返回 FALSE。
• 类似地，我们需要使用以下条件匹配成本条件：
  699=D2:D9
  这样，我们就将范围 D2:D9（即我们的成本列）的值转换为布尔值。当成本为 699 时，它返回 TRUE；否则返回 FALSE。
• 接下来，我们需要使用以下条件匹配第三个条件“Quantity”：
  6=E2:E9
  这样，我们将范围 E2:E9（代表数量）的值转换为布尔值。当数量为 6 时，它返回 TRUE；否则返回 FALSE。
• 之后，我们需要将所有三个条件的​​结果相乘。这将是所有所需条件的​​交集，并将布尔 TRUE 和 False 转换为 1 和 0。
• 在将布尔 TRUE/FALSE 转换为 1/0 之后，结果值将是包含 0 或 1 的列。现在，我们需要应用 MATCH 函数来定位包含值为 1 的相应列的行位置。如果列的值为 1，则满足我们示例数据的所有三个条件。
• 一旦提取了行位置，我们就在 INDEX 函数中提供它，以在以下位置查找相应行的总成本：
  =INDEX(F2:F9,MATCH(1,("Keyboard"=B2:B9)*(699=D2:D9)*(6=E2:E9),0))
  其中范围 F2:F9 代表包含项目总成本的列。
  

INDEX-MATCH 相对于 VLOOKUP 的优点

• INDEX-MATCH 更快、更易于使用。
• INDEX-MATCH 可以用于垂直和水平范围。
• INDEX-MATCH 可以用于左查找和右查找。
• INDEX-MATCH 可以同时用于升序和降序数据排列。
• INDEX-MATCH 与列的实际位置无关。