在Excel中，MATCH函數(shù)和INDEX函數(shù)是一對非常經(jīng)典的組合，我們經(jīng)常能夠在Excel公式中看到他倆的“身影”。MATCH函數(shù)返回查找值在單元格區(qū)域或者數(shù)組中的位置，INDEX函數(shù)返回這個位置的數(shù)據(jù)。下面，讓我們看看MATCH函數(shù)和INDEX函數(shù)組合使用的一些例子，從中體會這對組合的強(qiáng)大威力。

一鍵直達(dá)>> Excel函數(shù)學(xué)習(xí)1：MATCH函數(shù)

一鍵直達(dá)>> Excel函數(shù)學(xué)習(xí)2：INDEX函數(shù)

查找滿足多個條件的數(shù)據(jù)

如下圖1所示的工作表，數(shù)據(jù)區(qū)域?yàn)?/span>B3:D16，求單元格G2中指定班級和單元格G3中指定姓名的學(xué)生成績？在單元格G4中使用數(shù)組公式：

=INDEX(D3:D16,MATCH(G2&G3,B3:B16&C3:C16,0))

其中，MATCH(G2&G3,B3:B16&C3:C16,0)查找到指定班級的學(xué)生在數(shù)據(jù)區(qū)域中的位置，作為INDEX函數(shù)的參數(shù)來提取值。

圖1

總是獲取列表中的最后一個數(shù)據(jù)

如下圖2所示的工作表，求列表區(qū)域B3:D16中最后一位同學(xué)的成績？在單元格H4中的公式：

=INDEX($D$3:$D$16,MATCH(9.9E+307,$D$3:$D$16))

其中，MATCH(9.9E+307,$D$3:$D$16)總是獲取D3:D16中最后一個數(shù)據(jù)所在的位置。

圖2

創(chuàng)建動態(tài)區(qū)域

動態(tài)區(qū)域就是當(dāng)該區(qū)域中增加或刪除數(shù)據(jù)時，引用的區(qū)域會自動調(diào)整。

仍以圖2所示的工作表為例，將上例中的公式作為單元格引用的第二個元素：

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

因?yàn)?/span>INDEX函數(shù)在引用的開始單元格和冒號之后，因此不再獲取該區(qū)域中的最后一個單元格值，而是獲取區(qū)域中最后一個數(shù)據(jù)單元格的地址，從而與開始單元格組成單元格區(qū)域。

為了演示效果，我們將上面的引用定義為名稱：DynamicData，如下圖3所示，當(dāng)增加數(shù)據(jù)后，引用區(qū)域會自動擴(kuò)展。

圖3

創(chuàng)建更加強(qiáng)大的動態(tài)區(qū)域

下面是《Excel實(shí)戰(zhàn)技巧2：創(chuàng)建動態(tài)命名區(qū)域的3個公式》中的例子，使用INDEX函數(shù)、MATCH函數(shù)和COUNTA函數(shù)結(jié)合的公式，根據(jù)指定的列名創(chuàng)建動態(tài)區(qū)域，很特別的是，各列的行數(shù)不需要相同。

如下圖4所示，當(dāng)工作表Sheet4中單元格A1內(nèi)容為“水果”時，動態(tài)命名區(qū)域?yàn)楣ぷ鞅?/span>Sheet3中的水果列；當(dāng)工作表Sheet4中單元格A1內(nèi)容為“家用電器”時，動態(tài)命名區(qū)域?yàn)楣ぷ鞅?/span>Sheet3中的家用電器列，依此類推。

圖4

首先，工作表Sheet3中創(chuàng)建一個動態(tài)命名區(qū)域：Datas。

公式為：

=Sheet3!$A$2:INDEX(Sheet3!$1:$50,50,COUNTA(Sheet3!$1:$1))

如果工作表Sheet3如下圖5所示，則創(chuàng)建的動態(tài)區(qū)域?yàn)閺牧?/span>A開始的3列以及從第1行開始的50行的區(qū)域。

圖5

然后，選中工作表Sheet4的單元格B2，如圖6所示，打開“新建名稱”對話框，創(chuàng)建動態(tài)名稱：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

注意：由于要想引用當(dāng)前單元格左側(cè)的單元格，因此在定義名稱時，一定要選擇工作表Sheet4的單元格B1。

上面的公式比較復(fù)雜，以冒號為界，分為兩個部分。

第一部分：INDEX(Datas,1,MATCH(Sheet4!A1,Sheet3!$1:$1,0))

在工作表Sheet3中找到工作表Sheet4單元格A1中的數(shù)據(jù)所在的單元格，作為起始單元格。

在第二部分中，公式：COUNTA(INDEX(Datas,,MATCH(Sheet4!A1,Sheet3!$1:$1,0)))

找到工作表Sheet4單元格A1中的數(shù)據(jù)在工作表Sheet3中的列并統(tǒng)計(jì)該列非空單元格數(shù)量，作為外層INDEX函數(shù)的參數(shù)。整個第二部分的INDEX公式找到相應(yīng)列的最后一個數(shù)據(jù)單元格，作為結(jié)束單元格。

結(jié)語

使用MATCH函數(shù)與INDEX函數(shù)的組合，讓我們突破VLOOKUP函數(shù)的局限，創(chuàng)建常用的獲取數(shù)據(jù)的公式。如果更深入的發(fā)掘MATCH函數(shù)與INDEX函數(shù)的能力，可以創(chuàng)建更加強(qiáng)大的獲取數(shù)據(jù)區(qū)域的公式，使其發(fā)揮得淋漓盡致。

本文屬原創(chuàng)文章，轉(zhuǎn)載請注明出處。

歡迎在下面留言，完善本文內(nèi)容，讓更多的人學(xué)到更完美的知識。