Excel｜你還是好好學(xué)習(xí)一下INDEX+MATCH吧

韓老師入選國(guó)家十三五規(guī)劃的書：

【問(wèn)題來(lái)源】

原來(lái)，韓老師講過(guò)很多VLOOKUP查詢函數(shù)的使用。

VLOOKUP查詢有兩個(gè)不便之處：

1、查閱值（第一個(gè)參數(shù)）始終位于查找區(qū)域（第二個(gè)參數(shù)）的第一列；

2、從左向右查找容易，但從右向左查找需要IF或CHOOSE構(gòu)建新的查找區(qū)域。

今天，韓老師來(lái)講述另一功能更強(qiáng)大的組合：INDEX+MATCH，這一組合可以不受以上兩個(gè)條件的限制。

【MATCH函數(shù)】

MATCH函數(shù)查找指定項(xiàng)在單元格區(qū)域中的相對(duì)位置，即第幾行第幾列。

語(yǔ)法：MATCH(lookup_value,lookup_array, [match_type])

中文語(yǔ)法：MATCH(指定項(xiàng),單元格區(qū)域,[匹配方式])

match_type，即匹配方式，參數(shù)有三個(gè)：

• -1，查找小于或等于 lookup_value的最大值；

• 0，查找等于 lookup_value的第一個(gè)值；

• 1，查找大于或等于 lookup_value的最小值。

MATCH函數(shù)是查找函數(shù)最好的“搭檔”，在與INDEX、VLOOKUP、HLOOKUP配合使用中起到重要作用。

舉例

1、查所在行：

在B13輸入公式：=MATCH(A13,A2:A10,0)，即可查找不同姓名在第幾行。

2、查所在列：

在B6輸入公式：=MATCH(A6,A1:E1,0)，即可查找產(chǎn)品在第幾行。

【INDEX函數(shù)】

功能：查找單元格區(qū)域或數(shù)組常量中某行、某列或行列交叉點(diǎn)的值

語(yǔ)法：INDEX(array,row_num, [column_num])

中文語(yǔ)法：INDEX(單元格區(qū)域或數(shù)組常量,數(shù)組中的某行,[數(shù)組中的某列])

舉例

1、查詢不同銷售業(yè)績(jī)的銷售員姓名：

公式：=INDEX(A2:A10,MATCH(D2,B2:B10,0))

其中：MATCH(D2,B2:B10,0)，是D2單元格銷量業(yè)績(jī)?cè)谒袖N量業(yè)績(jī)中位于第幾行。

2、查詢不同不同銷量的產(chǎn)品名稱：

公式：=INDEX(A1:E1,MATCH(A6,A2:E2,0))

其中：MATCH(A6,A2:E2,0)，是A6單元格銷量在第幾列。

【INDEX+MATCH組合用法舉例】

1、查找業(yè)績(jī)最高姓名

D2單元格輸入公式：

=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0))

即可得到最高業(yè)績(jī)對(duì)應(yīng)姓名。

公式解釋：

2、查找業(yè)績(jī)第一二三姓名

在E2輸入公式：

=INDEX($A$2:$A$10,MATCH(LARGE($B$2:$B$10,ROW(A1)),$B$2:$B$10,0))

公式向下填充，可得前三名的姓名：

公式解釋：

本公式中應(yīng)用了LARGE函數(shù)，在B2:B10單元格查找第ROW(A1)大的值，公式在E2單元格時(shí)候，ROW(A1)返回值是1，向下填充時(shí)，會(huì)自動(dòng)變?yōu)镽OW(A2)、ROW(A3)，即第二大、第三大的值，從而查找出前三名的姓名。

特別注意：

INDEX+MATCH配合使用時(shí)，INDEX第一個(gè)參數(shù)區(qū)域，一定要和MATCH的第二個(gè)參數(shù)區(qū)域起始行一致，否則，會(huì)出現(xiàn)查找錯(cuò)位的情況。

3、查找行列交叉點(diǎn)的數(shù)值

C13輸入公式：=INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0))

即可實(shí)現(xiàn)指定銷售員指定產(chǎn)品的銷量，如下動(dòng)圖：

公式解析

MATCH(A13,A1:A10,0)：A13單元格姓名，在A1:A10區(qū)域中所在行。

MATCH(B13,A1:E1,0)：B13單元格產(chǎn)品，在A1:E1區(qū)域中所在列。

INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0))：A1:E10區(qū)域中，A13姓名所在行與B13產(chǎn)品所在列交叉點(diǎn)的值。

4、提取整行整列

（1）、查找指定姓名所有產(chǎn)品的銷量

選中B13:E13區(qū)域，輸入公式：=INDEX(B2:E10,MATCH(A13,A2:A10,0),0)

以Ctrl+Shift+Enter結(jié)束，如下圖：

即可完成查找。

（2）、查找所有姓名指定產(chǎn)品的銷量

選中H2:H10區(qū)域，輸入公式：=INDEX(B2:E10,0,MATCH(H1,B1:E1,0))

以Ctrl+Shift+Enter結(jié)束，如下圖：

即可完成查找。