二维查找中Excel中的索引匹配

教程展示了一些不同的公式，以在Excel中执行二维查找。只需浏览替代方案，然后选择您的喜欢:)

在搜索Excel电子表格中的某些内容时，大多数时候您会在列中或水平浏览。但是有时您需要浏览行和列。换句话说，您的目标是在某个行和列的交集处找到一个值。这称为Matrix查找（又称2维查找），该教程显示了如何以4种不同的方式进行操作。

• 索引匹配与行和列查找
• 使用Vlookup的双向查找
• Xlookup的2向查找
• 二维查找和苏马的产品
• 带有命名范围的矩阵查找（显式交叉）

Excel索引匹配公式

在Excel中进行双向查找的最流行方式是使用索引匹配。这是经典索引匹配公式的一种变体，您可以在其中添加一个匹配功能，以便获得行号和列号：

索引（ data_array ，match（ vlookup_value ， lookup_column_range ，0），匹配（ hlookup value ， lookup_row_range ，0））

例如，让我们制作一个公式，以从下表中从给定的一年中拉出某种动物的种群。对于初学者，我们定义了所有论点：

• data_array -b2：e4（数据单元，不包括行和列标题）
• vlookup_value -H1（目标动物）
• Lookup_column_range -A2：A4（行标头：动物名称） - A3：A4
• Hlookup_value -H2（目标年）
• Lookup_row_range -B1：E1（列标题：年）

将所有参数放在一起，您将获得此公式进行双向查找：

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

如果您需要使用两个以上标准进行双向查找，请查看本文：索引与行和列中的多个标准匹配。

该公式如何工作

乍一看可能看起来有些复杂，但公式的逻辑确实很简单且易于理解。索引函数根据行和列号从数据阵列中检索一个值，并且两个匹配函数提供了这些数字：

INDEX(B2:E4, row_num, column_num)

在这里，我们利用匹配的能力（lookup_value，lookup_array，[match_type]）返回lookup_value在lookup_array中的相对位置。

因此，要获取行号，我们在行标头（A2：A4）上搜索感兴趣的动物（H1）：

MATCH(H1, A2:A4, 0)

要获取列号，我们在整个列标题（B1：E1）上搜索目标年度（H2）：

MATCH(H2, B1:E1, 0)

在这两种情况下，我们都通过将第三个参数设置为0来查找确切的匹配。

在此示例中，第一匹匹配返回2，因为我们的Vlookup值（北极熊）在A3中找到，该值是A2：A4中的2^个单元格。第二个匹配返回3，因为在D1中发现了Hlookup值（2000），即B1：E1中的3 ^rd单元格。

鉴于上述，该公式减少为：

INDEX(B2:E4, 2, 3)

并在数据阵列b2：e4中的^第2行和3^列列的相交处返回一个值，该值是单元格D3中的一个值。

vlookup和2向查找的匹配公式

在Excel中进行二维查找的另一种方法是使用vlookup和匹配函数的组合：

vlookup（ vlookup_value ， table_array ，match（ hlookup_value ， lookup_row_range ，0），false），false）

对于我们的示例表，公式采用以下形状：

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

在哪里：

• table_array -a2：e4（包括行标头的数据单元格）
• vlookup_value -H1（目标动物）
• Hlookup_value -H2（目标年）
• Lookup_row_range -A1：E1（列标题：年）

该公式如何工作

公式的核心是为精确匹配配置的vlookup函数（最后一个参数设置为false），该函数在表阵列（A2：e4）的第一列中搜索查找值（H1），并从同一行中的另一列返回一个值。要确定要从哪个列返回值的列，您使用的匹配函数也配置为确切匹配（最后一个参数设置为0）：

MATCH(H2, A1:E1, 0)

匹配搜索在列标头（A1：E1）的H2中的值，并返回发现的单元格的相对位置。在我们的情况下，目标年（2010年）在E1中发现，该阵列位于查找阵列中的^第5个。因此，数字5转到vlookup的col_index_num参数：

VLOOKUP(H1, A2:E4, 5, FALSE)

Vlookup从那里拿走它，找到A2中其查找值的确切匹配，并从同一行中的^第5列返回一个值，即单元E2。

重要说明！为了使公式正确工作，vookup的table_array （A2：e4）和Match的Lookup_array （A1：E1）必须具有相同数量的列，否则匹配的数字通过col_index_num传递给col_index_num将不正确（与table_array中的列的位置不符）。

xlookup函数以行和列查看

最近，Microsoft在Excel中引入了另一个功能，该功能旨在替换所有现有的查找功能，例如VlookUp，Hlookup和Index Match。除其他外，Xlookup可以查看特定行和列的交集：

Xlookup（ vlookup_value ， vlookup_column_range ，xlookup（ hlookup_value ， hlookup_row_range ， data_array ））

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

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

笔记。 Xlookup功能仅在Microsoft 365，Excel 2021和excel的Excel中可用。

该公式如何工作

该公式使用Xlookup返回整个行或列的能力。内部功能在标题行中搜索目标年份，并返回该年的所有值（在本示例中为1980年）。这些值转到外部Xlookup的return_array参数：

XLOOKUP(H1, A2:A4, {22000;25000;700}))

外部Xlookup函数在列标头上搜索目标动物，并从return_array返回相同位置的值。

双向查找的Sumproduct公式

Sumproduct功能就像Excel中的瑞士刀一样 - 它可以做很多超出其指定目的的事情，尤其是在评估多个标准时。

要查找以行和列中的两个标准，请使用此通用公式：

sumproduct（ vlookup_column_range = vlookup_value ） *（ hlookup_row_range = hlookup_value ）， data_array ）

要在我们的数据集中执行2条查找，该公式如下：

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

以下语法也将起作用：

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

该公式如何工作

在公式的核心中，我们将两个查找值与行和柱标题（H1中的目标动物与A2：A4中的所有动物名称和H2中的所有动物名称与B1：E1中的所有年份）进行了比较）：

(A2:A4=H1) * (B1:E1=H2)

这将产生2个真实和错误值的数组，其中True's代表匹配：

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

乘法操作将真实值和错误值胁迫到1和0，并产生4列和3行的二维数组（行被分号分离，每列数据列以逗号分隔）：

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

SumProduct的功能将上述数组的元素乘以同一位置的B2：E4的项目：

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

而且由于乘以零的乘以零，因此仅在第一个数组中对应于1的项目幸存下来：

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

最后，sumproduct添加了结果数组的元素，并返回了2000年的值。

笔记。如果您的表具有具有相同名称的一行或/和列标题，则最终数组将包含以上的一个以上的数字，并且所有这些数字都将被添加。结果，您将获得符合这两个条件的值的总和。这就是使Sumproduct公式与索引匹配和Vlookup不同的原因，后者返回第一个发现的匹配。

带有命名范围的矩阵查找（显式交叉）

在Excel中进行矩阵查找的一种更简单的方法是使用命名范围。以下是：

第1部分：名称列和行

命名每一行的最快方法和表格中的每一列是：

1. 选择整个表（在我们的情况下，A1：E4）。
2. 在“公式”选项卡上，在定义的名称组中，单击“从选择”或按CTRL Shift F3快捷方式。
3. 在“从选择”对话框的“创建名称”中，选择顶行和左列，然后单击“确定”。

   

这将根据行和列标题自动创建名称。但是，有几个警告：

• 如果您的列和/或行标头是数字或包含Excel名称中不允许的特定字符，则不会创建此类列和行的名称。要查看创建名称的列表，请打开名称管理器（ CTRL F3 ）。如果缺少某些名称，请按照如何在Excel中命名范围中的解释。
• 如果您的某些行或列标题包含空格，则将其空间替换为下划线，例如Polar_bear 。

对于我们的示例表，Excel仅自动创建了行名。由于列标题是数字，因此必须手动创建列名。为了克服这一点，您可以简单地用_1990来将数字置于下划线。

结果，我们有以下命名范围：

第2部分：制作矩阵查找公式

要在给定的行和列的交点上拉一个值，只需在空单元格中键入以下通用公式之一：

= row_name column_name

反之亦然：

= column_name row_name

例如，为了在1990年获得蓝鲸的种群，该公式很简单：

=Blue_whale _1990

如果某人需要更详细的说明，以下步骤将使您完成该过程：

1. 在您希望结果出现的单元格中，键入相等符号（=）。
2. 开始键入目标行的名称，例如blue_whale 。输入几个字符后，Excel将显示所有与输入匹配的现有名称。双击所需的名称以将其输入您的公式：

   

3. 在行名之后，键入A空间，在这种情况下，该空间可用作交集操作员。
4. 输入目标列名称（在我们的情况下为_1990 ）。

   

5. 一旦输入了行和列名，Excel将突出显示表中的相应行和列，然后按Enter完成公式：

   

您的矩阵查找已完成，以下屏幕截图显示了结果：

这就是如何在Excel中以行和列查找的方法。我感谢您阅读，并希望下周在我们的博客上见到您！

可用下载

二维查找样本工作簿

以上是二维查找中Excel中的索引匹配的详细内容。更多信息请关注PHP中文网其他相关文章！