Excel索引与多个标准匹配

首页

专题

excel

Excel索引与多个标准匹配 - 公式示例

William Shakespeare

Apr 02, 2025 am 09:29 AM

本教程显示了如何使用索引和匹配以及其他几种方式在Excel中使用多个标准查找。

尽管Microsoft Excel为垂直和水平查找提供了特殊功能，但专家用户通常会用索引匹配替换它们，这在许多方面都优于vlookup和hlookup。除其他外，它可以在列和行中查找两个或多个标准。本教程详细说明了语法和内部力学，以便您可以轻松地调整特定需求的公式。为了使示例更容易遵循，欢迎您下载我们的示例工作簿。

在使用大型数据库时，您有时可能会发现自己需要找到某些东西，但没有唯一的搜索标识符。在这种情况下，有几种条件的查找是唯一的解决方案。

要根据单独列中的多个标准查找值，请使用此通用公式：

{= index（ return_range ，match（1，（ criteria1 = range1 ） *（ criteria2 = range2 ） *（…），0）}}}}

在哪里：

return_range是返回值的范围。
标准1 ，标准2 ，…是要满足的条件。
Range1 ， Range2 ，…是应测试相应标准的范围。

重要说明！这是一个数组公式，必须在Ctrl Shift Enter中完成。这将使您的公式包含在{curly brokkets}中，这是Excel中数组公式的视觉迹象。不要尝试手动键入牙套，那将行不通！

公式是标志性索引匹配的高级版本，该版本根据单个标准返回匹配项。为了评估多个标准，我们使用在数组公式中用作和运算符的乘法操作。下面，您将找到一个现实生活中的示例和逻辑的详细说明。

提示。在Excel 365和2021中，您可以使用具有多个标准的Xlookup公式。

索引与多个标准匹配 - 公式示例

在此示例中，我们将在其自己的行上使用所谓的“扁平文件”格式中的表格（在我们的情况下为月度数字）。我们的目标是在特定地区和月份中检索某些项目的销售数字。

借助以下单元格中的源数据和标准：

return_range （销售）-D2：D13
标准1 （目标区域）-G1
标准2 （目标月）-G2
标准3 （目标项目）-G3
Range1 （区域）-A2：A13
范围2 （月）-B2：B13
范围3 （项目）-C2：C13

该公式采用以下形状：

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

输入公式，例如在G4中，通过按CTRL Shift Enter来完成它，您将获得以下结果：

Excel索引与多个标准匹配 - 公式示例

该公式如何工作

最棘手的部分是匹配功能，所以让我们首先弄清楚：

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

您可能还记得，匹配（lookup_value，lookup_array，[match_type]）在查找数组中搜索查找值，并在数组中返回该值的相对位置。

在我们的公式中，论点如下：

Lookup_value ：1
Lookup_array ：（ g1 = a2：a13） *（g2 = b2：b13） *（g3 = c2：c13）
match_type ：0

第1^个参数是晶体清晰的 - 函数搜索数字1。设置为0的3 ^rd参数表示“精确匹配”，即公式返回第一个发现的值，该值完全等于查找值。

问题是 - 为什么我们要搜索“ 1”？要获取答案，让我们仔细查看查找阵列，在该阵列中，我们将每个标准与相应的范围进行比较：G1中的目标区域与所有区域（A2：A13）（A2：A13），G2中的目标月对所有月份（B2：B13）和G3中的目标项目（C2：C2：C13）。中间结果是3个真实和错误的阵列，而true表示满足已测试条件的值。要可视化这一点，您可以在公式中选择单个表达式，然后按F9键查看每个表达式评估的内容：

Excel索引与多个标准匹配 - 公式示例

乘法操作分别将真实值和错误值分别转换为1和0：

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

而且由于乘以0总是给出0，因此结果数组仅在满足所有标准的行中只有1个：

{0;0;1;0;0;0;0;0;0;0;0;0}

上面的数组转到匹配的Lookup_array参数。使用Lookup_value为1，该函数返回所有标准为真的行的相对位置（在我们的情况下第3行）。如果数组中有几个1，则返回第一个位置。

匹配返回的数字直接转到索引的row_num参数（array，row_num，[column_num]）函数：

=INDEX(D2:D13, 3)

它产生的结果为$ 115，这是D2：D13数组中的3 ^rd值。

具有多个标准的非阵列索引匹配公式

上一个示例中讨论的阵列公式适合经验丰富的用户。但是，如果您正在为他人建立一个公式，并且某人不知道数组功能，他们可能会无意中打破它。例如，用户可以单击您的公式检查它，然后按Enter而不是Ctrl Shift Enter 。在这种情况下，明智的做法是避免阵列并使用更具防弹性的常规公式：

index（ return_range ，match（1，index（（ criteria1 = range1 ） *（ criteria2 = range2 ） *（..），0，1），0），0））

对于我们的示例数据集，公式如下：

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Excel索引与多个标准匹配 - 公式示例

该公式如何工作

由于索引函数可以本地处理数组，因此我们添加了另一个索引来处理1和0的数组，该数组是通过乘以两个或多个true/false数组创建的。第二个索引配置为0 row_num参数，以使公式返回整个列数组而不是单个值。由于它是一个单列数组，因此我们可以安全地为Column_num提供1：

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

此数组传递给匹配功能：

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

匹配找到所有标准为真的行（更准确地说，是指定数组中该行的相对位置），并将该编号传递给第一个索引的Row_num参数：

=INDEX(D2:D13, 3)

索引与行和列中的多个标准匹配

此示例显示了如何通过在行和列中测试两个或多个标准来执行查找。实际上，这是一个更复杂的情况，即带有多个标头行的所谓“矩阵查找”或“双向查找”。

这是一行和列中有多个标准的通用索引匹配公式：

{= index（ table_array ，match（ vlookup_value ， lookup_column ，0），match（ hlookup_value1 ＆ hlookup_value2 ， lookup_row1 ＆lookup_row1＆ lookup_row2，0 ）}）}））}）

在哪里：

table_array-在内部搜索的地图或区域，即所有数据值不包括列和行标头。

vlookup_value-您在列中垂直寻找的值。

Lookup_column-通常要搜索的列范围，通常是行标头。

hlookup_value1，hlookup_value2，… - 在行中水平寻找的值。

Lookup_row1，Lookup_row2，… - 行搜索范围，通常是列标题。

重要说明！为了使公式正确工作，必须将其作为带有Ctrl Shift Enter的数组公式输入。

它是经典的双向查找公式的变体，该公式在某个行和列的交点处搜索一个值。不同之处在于，您将几个Hlookup值和范围串联以评估多个列标题。为了更好地理解逻辑，请考虑以下示例。