MATCH函数+INDEX函数组合 _知识经验

在Excel中，MATCH函数和INDEX函数是一对非常经典的组合，我们经常能够在Excel公式中看到他俩的“身影” 。MATCH函数返回查找值在单元格区域或者数组中的位置，INDEX函数返回这个位置的数据。下面，让我们看看MATCH函数和INDEX函数组合使用的一些例子，从中体会这对组合的强大威力。
查找满足多个条件的数据
如下图1所示的工作表，数据区域为B3:D16，求单元格G2中指定班级和单元格G3中指定姓名的学生成绩？在单元格G4中使用数组公式：
=INDEX(D3:D16,MATCH(G2&G3,B3:B16&C3:C16,0))
其中，MATCH(G2&G3,B3:B16&C3:C16,0)查找到指定班级的学生在数据区域中的位置，作为INDEX函数的参数来提取值。

文章插图
图1
总是获取列表中的最后一个数据
如下图2所示的工作表，求列表区域B3:D16中最后一位同学的成绩？在单元格H4中的公式：
【MATCH函数+INDEX函数组合】=INDEX($D$3:$D$16,MATCH(9.9E+307,$D$3:$D$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中的水果列；当工作表Sheet4中单元格A1内容为“家用电器”时，动态命名区域为工作表Sheet3中的家用电器列，依此类推。

文章插图
图4
首先，工作表Sheet3中创建一个动态命名区域：Datas 。
公式为：
=Sheet3!$A$2:INDEX(Sheet3!$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
注意：由于要想引用当前单元格左侧的单元格，因此在定义名称时，一定要选择工作表Sheet4的单元格B1 。
上面的公式比较复杂，以冒号为界，分为两个部分。
第一部分：INDEX(Datas,1,MATCH(Sheet4!A1,Sheet3!$1:$1,0))
在工作表Sheet3中找到工作表Sheet4单元格A1中的数据所在的单元格，作为起始单元格。
在第二部分中，公式：COUNTA(INDEX(Datas,,MATCH(Sheet4!A1,Sheet3!$1:$1,0)))