【問題來源】
原來,韓老師講過很多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
結束,如下圖:
即可完成查詢。