Excel｜你還是好好學習一下INDEX+MATCH吧

韓老師入選國家十三五規劃的書：

【問題來源】

原來，韓老師講過很多VLOOKUP查詢函數的使用。

VLOOKUP查詢有兩個不便之處：

1、查閱值（第一個參數）始終位于查找區域（第二個參數）的第一列；

2、從左向右查找容易，但從右向左查找需要IF或CHOOSE構建新的查找區域。

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

【MATCH函數】

MATCH函數查找指定項在單元格區域中的相對位置，即第幾行第幾列。

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

中文語法：MATCH(指定項,單元格區域,[匹配方式])

match_type，即匹配方式，參數有三個：

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

• 0，查找等于 lookup_value的第一個值；

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

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

舉例

1、查所在行：

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

2、查所在列：

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

【INDEX函數】

功能：查找單元格區域或數組常量中某行、某列或行列交叉點的值

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

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

舉例

1、查詢不同銷售業績的銷售員姓名：

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

其中：MATCH(D2,B2:B10,0)，是D2單元格銷量業績在所有銷量業績中位于第幾行。

2、查詢不同不同銷量的產品名稱：

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

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

【INDEX+MATCH組合用法舉例】

1、查找業績最高姓名

D2單元格輸入公式：

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

即可得到最高業績對應姓名。

公式解釋：

2、查找業績第一二三姓名

在E2輸入公式：

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

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

公式解釋：

本公式中應用了LARGE函數，在B2:B10單元格查找第ROW(A1)大的值，公式在E2單元格時候，ROW(A1)返回值是1，向下填充時，會自動變為ROW(A2)、ROW(A3)，即第二大、第三大的值，從而查找出前三名的姓名。

特別注意：

INDEX+MATCH配合使用時，INDEX第一個參數區域，一定要和MATCH的第二個參數區域起始行一致，否則，會出現查找錯位的情況。

3、查找行列交叉點的數值

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

即可實現指定銷售員指定產品的銷量，如下動圖：

公式解析

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

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

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

4、提取整行整列

（1）、查找指定姓名所有產品的銷量

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

以Ctrl+Shift+Enter結束，如下圖：

即可完成查找。

（2）、查找所有姓名指定產品的銷量

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

以Ctrl+Shift+Enter結束，如下圖：

即可完成查找。