MATCH函數+INDEX函數組合

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中的公式：

=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)))

找到工作表Sheet4單元格A1中的數據在工作表Sheet3中的列并統計該列非空單元格數量，作為外層INDEX函數的參數。整個第二部分的INDEX公式找到相應列的最后一個數據單元格，作為結束單元格。

結語

使用MATCH函數與INDEX函數的組合，讓我們突破VLOOKUP函數的局限，創建常用的獲取數據的公式。如果更深入的發掘MATCH函數與INDEX函數的能力，可以創建更加強大的獲取數據區域的公式，使其發揮得淋漓盡致。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。