MATCH函数INDEX函数组合

在Excel中，MATCH函数和INDEX函数是非常经典的组合，我们经常可以在Excel公式中看到它们的“影子”。MATCH函数返回单元格区域或数组中搜索值的位置，INDEX函数返回该位置的数据。下面我们来看一些MATCH函数和INDEX函数结合的例子，体会一下这种结合的强大威力。

查找满足多个条件的数据。

在下图1所示的工作表中，数据区为B3:D16，以便在单元格G2中查找指定班级的学生成绩，在单元格G3中查找指定姓名的学生成绩。在单元格G4中使用数组公式：

=INDEX(D3:D16，MATCH(G2G3，B3:B16C3:C16，0))

Match (g2g3，b3:b16c:c16，0)在数据区找到指定班级学生的位置，并提取该值作为INDEX函数的参数。

图1

总是获取列表中的最后一个数据。

在下图2所示的工作表中，在列表区域B3:D16中找到最后一名学生的成绩。单元格H4中的公式：

=INDEX(3:加元16加元，MATCH(9.9E 307加元：加元16加元))

其中，MATCH(9.9E 307，$ D 3: $ D 16)总是获取D3:D16中最后一个数据的位置。

图2。

创建动态区域。

动态区域是指在该区域增加或删除数据时，被引用区域会自动调整。

仍然以图2所示的工作表为例，将前面示例中的公式作为单元格引用的第二个元素：

B $ 3: INDEX(D $ 3:D $ 16，MATCH(9.9E 307，D $ 3:D $ 16))

因为INDEX函数位于引用的起始单元格和冒号之后，所以它不再获取区域中的最后一个单元格值，而是获取区域中最后一个数据单元格的地址，从而与起始单元格形成一个单元格区域。

为了演示这个效果，我们将上面的引用定义为名称：DynamicData，如下图3所示。添加数据后，参考区域将自动扩展。

图3。

创建一个更强大的动态区域。

在下面的示例中，使用INDEX函数、MATCH函数和COUNTA函数相结合的公式，根据指定的列名创建了动态区域。特别是，每列中的行数不需要相同。

如下图4所示，当工作表Sheet4中单元格A1的内容为“水果”时，动态命名区域为工作表Sheet3中的水果列。当工作表4中单元格A1的内容为“家用电器”时，动态命名区域为工作表3中的“家用电器”列，以此类推。

图4

首先，在工作表Sheet3中创建一个动态命名区域：Datas。

公式是：

=Sheet3！$ 2:澳元指数(第3页！$1:$50，50，COUNTA(Sheet3！$1:$1))

如果工作表Sheet3如下图5所示，则创建的动态区域是从列A开始的3列区域和从行1开始的50行区域。

图5

然后，选择工作表Sheet4的单元格B2，如图6所示，打开“新建名称”对话框，创建一个动态名称：DynamicList。

公式是：

=INDEX(Datas，1，MATCH(Sheet4！A1，Sheet3！$1:$1，0)):INDEX(Datas，COUNTA(INDEX(Datas，MATCH(Sheet4！A1，Sheet3！$1:$1，0))、MATCH(Sheet4！A1，Sheet3！$1:$1,0))

图6

注意：因为要引用当前单元格左侧的单元格，所以在定义名称时必须选择工作表4的单元格B1。

以上公式比较复杂，以冒号为界，分为两部分。

第一部分：索引(数据集，1，匹配(sheet4！A1，Sheet3！$1:$1,0))

在工作表Sheet3中，找到工作表Sheet4的单元格A1中的数据所在的单元格作为起始单元格。

在第二部分，公式是counta(index(data ), match(sheet 4！A1，Sheet3！$1:$1,0)))

在工作表3中找到工作表4的单元格A1中的数据列，并计算该列中非空单元格的数量，作为外部索引函数的参数。整个第二部分的INDEX公式将相应列的最后一个数据单元格作为结束单元格。

标签

利用MATCH函数和INDEX函数的结合，让我们突破VLOOKUP函数的限制，创建一个获取数据的通用公式。如果我们更深入地探索MATCH函数和INDEX函数的能力，就可以创建一个更强大的公式来获取数据区域，并使其发挥得淋漓尽致。