如何使用INDEX MATCH在Excel中执行高级查找

您是否曾在巨大的 Excel 电子表格中感到迷茫，尤其是当您需要沿行或列查找值时？您是否经常浏览大型 Excel 数据集以找出有趣的数据并手动创建这些数据的列表，并在此过程中给您的眼睛带来巨大的压力？那么，您必须了解如何在 Excel 中使用 INDEX MATCH 在 Excel 中执行高级查找。

在操作、分析和可视化数据集以提取可操作的见解时，您经常需要从大型数据集中查找查询或有趣的数据。我知道您可以逐行逐列地查找单个数据，记下这些数据，然后重复该过程。但是，如果您使用的是 Microsoft Excel，那真的不是您应该遵循的流程。

在这种情况下，您可以结合使用 INDEX 和 MATCH 函数，通过为 Excel 提供与查询数据相关的引用或提示来定位数据。

无论您是经验丰富的 Excel 用户还是刚开始使用 Excel 的新手，本文都将提供分步指南，帮助您充分利用 INDEX MATCH 的潜力。在本指南结束时，您将能够轻松执行复杂的查找，从而使您的数据分析更加高效和富有洞察力。让我们开始吧！

Excel 中的 INDEX 和 MATCH 是什么？

在了解单个 Excel 公式中 INDEX 和 MATCH 函数的技术用例之前，您必须了解这些公式的基础知识以及如何编写这些函数。

指数

如何使用INDEX MATCH在Excel中执行高级查找 — 使用 INDEX 检索单元格值

Excel 中的INDEX公式允许您根据行号和列号检索指定范围内单元格的值。这是一个强大的数据集处理函数，通常用于数据查找和数组操作任务。此公式对于动态数据分析非常有用，可让您根据特定条件提取信息。

Excel 中的 INDEX 公式语法是：

=INDEX(array, row_num, [column_num])

数组：这是您要从中检索值的数据范围或数组。它可以是单行、单列或多行和多列。数组应排除数据集的列标题。
Row_Num：这是您要从中检索值的数组中的行号。它可以是数字、数组或对包含数字的单个单元格的引用。行号计数根据突出显示的数组进行。如果您选择了A2:A10，则单元格A2为第 1 行，单元格A10为第 9 行。
[Column_Num]：这是您要从中检索值的数组中的列号。如果省略，列号默认与行号相同。如果数组是一维的，则它是可选的。仅当您包含由多列组成的查找范围时才需要它。

匹配

您可以使用Excel 中的MATCH公式来定位范围或数组中指定值的位置。它与 INDEX 函数正好相反。

Excel 中的 MATCH 公式语法是：

=MATCH(lookup_value, lookup_array, [match_type])

Lookup_Value：这是您想要在 lookup_array 中搜索的值。
Lookup_Array：这是 Excel 应该搜索Lookup_Value的单元格范围或数组。
[Match_Type]：此参数指定匹配的类型。这是一个可选参数。以下是Match_Type的符号和编码：
- 1 或省略：查找小于或等于Lookup_Value 的最大值，这是默认设置。
- 0：找到完全匹配，因此适合基于文本的值搜索。
- -1：查找大于或等于Lookup_Value 的最小值。

INDEX 函数需要行号来定位单元格的所需值，而 MATCH 函数可以生成该行号。

因此，通过使用 MATCH 作为 INDEX 的行参数，您可以轻松地根据海量数据集中的条件找到单个值。

INDEX MATCH 相对于 VLOOKUP 的优势

INDEX MATCH 可以在行和列中查找值，而 VLOOKUP 仅限于在列中搜索。
VLOOKUP 始终要求查找的值位于查找表的最左列。
INDEX MATCH 允许动态列选择，这意味着您可以轻松更改列引用而无需修改公式。
使用 INDEX MATCH，如果您重新排列数据集中的列，公式不会受到影响，而 VLOOKUP 需要调整列引用。
INDEX MATCH 可以更优雅地处理错误，仅在未找到匹配项时返回 #N/A，而如果列索引发生变化，VLOOKUP 可能会返回不正确的结果。
在大型数据集中，INDEX MATCH 由于其计算效率而往往比 VLOOKUP 表现更好。
INDEX MATCH 可以比 VLOOKUP 与辅助列结合更有效地处理多个条件查找。
INDEX MATCH 可以处理不连续的数据范围，而 VLOOKUP 不能。

简单的 INDEX 和 MATCH

让我们尝试理解 INDEX MATCH 组合公式在如上所示的简单数据集中的用法。在上面的数据集中，我将使用 INDEX 和 MATCH根据候选人的国家/地区查找候选人。

例如，我需要查找来自美国的候选人的姓名。

因此，首先我将在目标单元格中输入以下 MATCH 公式，以在查找表中找到目标数据所在的行号。

=MATCH(F1,B2:B7,0)

点击后Enter，我得到了目标单元格的行号。

现在，我将使用 INDEX 扩展相同的公式，使 MATCH 公式成为 INDEX 列的行参数。

这是公式的最终组合：

=INDEX(A2:A7,MATCH(F1,B2:B7,0))

我在目标单元格中输入了上述公式F2，然后按下Enter以获取查询值，即Margaret M。

使用 INDEX 和 MATCH 合并表数据

您还可以创造性地使用此组合公式将第二个表中的表格数据导入第一个表中，以使您的数据集更加全面和有条理。

例如，在上面显示的数据集中，您想要将回扣数据从表 2导入到表 1，这样您就可以摆脱第二个表。

因此，我将向您展示如何使用 Excel 中的 INDEX 和 MATCH通过引用表 2 中的分类折扣来分配表 1 中的折扣列下方的折扣百分比。

第一步是使用RebateD3列下方的第一个单元格中的以下 MATCH 公式通过匹配类别来找到行号：

=MATCH([@Category],$G$3:$G$7,0)

现在您已经获得了分类折扣值所在的行号，请扩展公式以使 MATCH 公式成为 INDEX 公式的行参数。然后，您只需突出显示Disc %列值，以便 INDEX 可以填充相应的单元格值D3。

=INDEX($F$3:$F$7,MATCH([@Category],$G$3:$G$7,0))

Enter输入后D3，您将获得0.1，即 MATCH 函数找到的第一行的值。

选择D3并单击Excel “主页”选项卡的“数字”命令块中的%符号，将小数转换为百分比值。

现在，将填充柄从D3下往上拖动，直到D12填充其余硬件产品的折扣百分比。

现在，您可以删除表 2来整理您的数据集。

使用 INDEX 和 MATCH 将数据提取到表

通常，您会得到非结构化和堆叠的数据集，您可以从中提取重要值到主表以进一步存储或分析数据。您可以使用 INDEX 和 MATCH 的组合公式来实现这一点。

在上述数据集中，供应商电子邮件以堆叠的方式记录，这对于实现任何自动化以按硬件类别提取供应商电子邮件并将其放在供应商电子邮件列下方都是一个挑战。

但是，如果对 INDEX MATCH 公式进行一些修改，您就可以在表中填充与产品类别相关的供应商电子邮件。

让我们在E 列中找到相关类别的行号，并将其放入供应商电子邮件C2列的单元格中。为此，您可以使用此MATCH公式。点击以获取行号。Enter

=MATCH(B2,$E$2:$E$11,0)

现在，您可以使用INDEX扩展上述公式，以填充检索到的行号中的单元格的值。在公式中，您必须添加1，以便 Excel 可以获取提取的行号下方的供应商电子邮件。以下是完整的公式语法：

=INDEX($E$2:$E$11,MATCH(B2,$E$2:$E$11,0)+1)

现在，只需使用填充柄将相同的 INDEX MATCH 公式向下扩展至供应商电子邮件列，即可获取其余硬件产品的供应商电子邮件。

INDEX 和 MATCH 与多个 MATCH 数组

有时，您可能希望在 MATCH 中使用多个查找值来获取行号，然后可以在 INDEX 中使用这些行号来获取预期值。

假设您有一个按国家和类别划分的产品折扣工作表，如上图所示。

现在，您要引用表 2中的折扣 %列，并在表 1的折扣列中填充适当的折扣百分比。

首先，您需要使用以下MATCH公式，该公式在单元格中包含两个查找数组E3：

=MATCH(1,($H$3:$H$12=B3)*($I$3:$I$12=C3),0)

上述公式根据表 1中的硬件产品，填充了表 2中的相关行号。

现在，使用INDEX公式扩展上述公式以获取相关的折扣金额E3。组合公式如下：

=INDEX($G$3:$G$12,MATCH(1,($H$3:$H$12=B3)*($I$3:$I$12=C3),0))

您将得到十进制值0.1 。您可以通过主页>数字> %E2按钮轻松将其转换为百分比值。

现在，使用填充柄将相同的公式和数字格式应用于表 1中Rebate列的其余部分。

如果您在早于 Excel for Microsoft 365 的 Excel 桌面版本中执行此方法，则必须按Ctrl+ Shift+Enter来计算结果，而不仅仅是Enter按键。

使用 INDEX 和 MATCH 进行按行和按列查找

到目前为止，您已经学习了结合使用 INDEX 和 MATCH 来按查询行获取单元格值的技术。但是，您也可以创建查找模型，通过引用行号和列号从查找表中生成单元格值。

在此方法中，您将使用两个MATCH函数来获取INDEX的行号和列号参数。反过来，您将获得一直在寻找的单元格值。

在上面的数据集中，我想为表 1中Rebate列下方的硬件产品分配折扣百分比。我分配折扣的参考表是表 2。表 2 包含按国家/地区划分的产品类别和折扣百分比列。

让我们使用以下公式创建行号的第一个 MATCH和列号的第二个 MATCHE3：

=MATCH([@Category],$G$3:$G$7,0),MATCH([@Country],$H$2:$I$2,0)

我现在还不能按Enter，因为这样做会导致 Excel 中出现公式错误。

我还必须将INDEX公式添加到上面的部分公式中。最终的 INDEX MATCH 公式如下所示：

=INDEX($H$3:$I$7,MATCH([@Category],$G$3:$G$7,0),MATCH([@Country],$H$2:$I$2,0))

现在，我可以简单地使用填充柄来填充“折扣”列中其余单元格的折扣值。

结论

到目前为止，您已经发现了使用Excel 中的INDEX和MATCH函数轻松使用真实数据集查找值的不同方法。

如果您尝试过上述所有或部分方法，请在下方评论您最喜欢哪种方法。此外，如果您知道有关如何在 Excel 中使用 INDEX MATCH 的更好技巧和窍门，请发表评论。