在Excel中，MATCH函數(shù)和INDEX函數(shù)是一對(duì)非常經(jīng)典的組合，我們經(jīng)常能夠在Excel公式中看到他倆的“身影”。MATCH函數(shù)返回查找值在單元格區(qū)域或者數(shù)組中的位置，INDEX函數(shù)返回這個(gè)位置的數(shù)據(jù)。下面，讓我們看看MATCH函數(shù)和INDEX函數(shù)組合使用的一些例子，從中體會(huì)這對(duì)組合的強(qiáng)大威力。
一鍵直達(dá)>> Excel函數(shù)學(xué)習(xí)1：MATCH函數(shù)
一鍵直達(dá)>> Excel函數(shù)學(xué)習(xí)2：INDEX函數(shù)
查找滿足多個(gè)條件的數(shù)據(jù)
如下圖1所示的工作表，數(shù)據(jù)區(qū)域?yàn)?/span>B3:D16，求單元格G2中指定班級(jí)和單元格G3中指定姓名的學(xué)生成績？在單元格G4中使用數(shù)組公式：
=INDEX(D3:D16,MATCH(G2&G3,B3:B16&C3:C16,0))
其中，MATCH(G2&G3,B3:B16&C3:C16,0)查找到指定班級(jí)的學(xué)生在數(shù)據(jù)區(qū)域中的位置，作為INDEX函數(shù)的參數(shù)來提取值。
圖1
總是獲取列表中的最后一個(gè)數(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中最后一個(gè)數(shù)據(jù)所在的位置。
圖2
創(chuàng)建動(dòng)態(tài)區(qū)域
動(dòng)態(tài)區(qū)域就是當(dāng)該區(qū)域中增加或刪除數(shù)據(jù)時(shí)，引用的區(qū)域會(huì)自動(dòng)調(diào)整。
仍以圖2所示的工作表為例，將上例中的公式作為單元格引用的第二個(gè)元素：
$B$3:INDEX($D$3:$D$16,MATCH(9.9E+307,$D$3:$D$16))
因?yàn)?/span>INDEX函數(shù)在引用的開始單元格和冒號(hào)之后，因此不再獲取該區(qū)域中的最后一個(gè)單元格值，而是獲取區(qū)域中最后一個(gè)數(shù)據(jù)單元格的地址，從而與開始單元格組成單元格區(qū)域。
為了演示效果，我們將上面的引用定義為名稱：DynamicData，如下圖3所示，當(dāng)增加數(shù)據(jù)后，引用區(qū)域會(huì)自動(dòng)擴(kuò)展。
圖3
創(chuàng)建更加強(qiáng)大的動(dòng)態(tài)區(qū)域
下面是《Excel實(shí)戰(zhàn)技巧2：創(chuàng)建動(dòng)態(tài)命名區(qū)域的3個(gè)公式》中的例子，使用INDEX函數(shù)、MATCH函數(shù)和COUNTA函數(shù)結(jié)合的公式，根據(jù)指定的列名創(chuàng)建動(dòng)態(tài)區(qū)域，很特別的是，各列的行數(shù)不需要相同。
如下圖4所示，當(dāng)工作表Sheet4中單元格A1內(nèi)容為“水果”時(shí)，動(dòng)態(tài)命名區(qū)域?yàn)楣ぷ鞅?/span>Sheet3中的水果列；當(dāng)工作表Sheet4中單元格A1內(nèi)容為“家用電器”時(shí)，動(dòng)態(tài)命名區(qū)域?yàn)楣ぷ鞅?/span>Sheet3中的家用電器列，依此類推。
圖4
首先，工作表Sheet3中創(chuàng)建一個(gè)動(dòng)態(tài)命名區(qū)域：Datas。
公式為：
=Sheet3!$A$2:INDEX(Sheet3!$1:$50,50,COUNTA(Sheet3!$1:$1))
如果工作表Sheet3如下圖5所示，則創(chuàng)建的動(dòng)態(tài)區(qū)域?yàn)閺牧?/span>A開始的3列以及從第1行開始的50行的區(qū)域。
圖5
然后，選中工作表Sheet4的單元格B2，如圖6所示，打開“新建名稱”對(duì)話框，創(chuàng)建動(dò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è)的單元格，因此在定義名稱時(shí)，一定要選擇工作表Sheet4的單元格B1。
上面的公式比較復(fù)雜，以冒號(hào)為界，分為兩個(gè)部分。
第一部分：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ù)。整個(gè)第二部分的INDEX公式找到相應(yīng)列的最后一個(gè)數(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)載請(qǐng)注明出處。
歡迎在下面留言，完善本文內(nèi)容，讓更多的人學(xué)到更完美的知識(shí)。
聯(lián)系客服
