常用的查找函數VLOOKUP用的較多,不過最佳搭檔莫過於INDEX+MATCH,今天來聊聊這對搭檔。
1、INDEX
2、MATCH
3、INDEX+MATCH最佳搭檔
1、INDEX
INDEX,指在給定的單元格區域中,返回特定行列交叉處單元格的值或引用,在單行或單列區域中查找時, 只需要使用行號,列號可省略,語法:=INDEX(區域,行號,列號)。
如下圖,在B3:A13單列區域中查找第5行的數據;在A3:H10區域中查找第7行第2列的數據,公式操作如下圖:
2、MATCH
MATCH, 返回符合特定值特定順序的項在數組中的相對位置,即在指定的單行或單列區域數組中, 查找搜索項第一次出現的位置。語法:=MATCH(查找值,單列或單行數組,查找方式)。
3、INDEX+MATCH最佳拍檔
①、INDEX+MATCH是一對最佳排擋,如下圖需要根據姓名查人事編號,在H4單元格輸入公式=INDEX(A:A,MATCH(I4,B:B,0)),即可實現逆向查詢,比常用的VLOOKUP來的實在。
公式解釋:
公式=MATCH(I4,B3:B13,0),用MATCH函數確定王五在B列中的位置,即返回第10行的位置。
公式=INDEX(A:A,10 ,0),利用INDEX在給定的單元格區域中,返回特定行列交叉處單元格的值或引用,即返回第10行A列對應的位置。
②、如下圖,需要查找趙錢2月的銷量,在G5單元格輸入公式=INDEX(B3:D13,MATCH(G4,A3:A13,0),MATCH(G3,B2:D2,0))。
公式解釋:利用兩個MATCH函數分別查找趙錢及2月在表中的位置,在根據INDEX返回它的引用。
③、轉置區域,如下圖根據A2:C5區域轉到一列(詳細INT及MOD函數用法介紹請看7月22及23日文章),從A2開始先列後行,E2單元格公式=INDEX($A$2:$C$5,INT(ROW(A3)/3),MOD(ROW(A3),3)+1)。
公式解釋:
公式=INT(ROW(A3)/3),確定INDEX的行號,得出結果3個1、3個2、3個3、3個4,即第一行,第二行,第三行,第四行的位置;
公式=MOD(ROW(A3),3)+1,確定列的位置,得出結果1、2、3的循環序列;
最後利用INDEX加上行和列號,返回相對應位置的數據,如下圖:
閱讀更多 Excel函數與VBA實例 的文章