Excel雙向查找的8個公式,全了!

在Excel表格中,雙向查找也稱交叉查找,即同時根據行和列的條件進行查找。今天分享8個查找公式並添加註釋,希望對大家有用。

【例】如下圖所示,要求在單元格D10設置公式,根據姓名和月份查找對應的銷售量。

Excel雙向查找的8個公式,全了!

公式1

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

注:這是最常用的雙向查找套路,利用Macth分別查找姓名所在行數和月份所在列數,然後用index(表格,行數,列數)提取對應的數值

公式2

=OFFSET($B$2,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

注:同公式1用match查找行、列數,但最後是用offset(目標單元格,偏移行數,偏移列數)提取對應的值。

公式3

=VLOOKUP(B10,$B$3:$H$7,MATCH(C10,$C$2:$H$2,0)+1,0)

注:用match函數查找3月(C10數據)所在列數,作為Vlookup(目標,表格,列數,0)函數的第3個參數。

公式4

=INDIRECT(ADDRESS(MATCH(B10,B1:B7,0),MATCH(C10,A2:H2,0),,,"sheet1"))

注:用match函數根據給出的姓名和月份查找行、列數,然後用address(行數,列數,絕對引用方式,是否為r1c1,表格名)組合成引用地址,然後用indirect(單元格地址)函數提取值。

公式5

=HLOOKUP(C10,C2:H7,MATCH(B10,B2:B7,0),0)

注:用match函數查找姓名(B10數據)所在行數,作為Hlookup(目標,表格,行數,0)函數的第3個參數。

公式6

=SUMPRODUCT(($B$3:$B$7=B10)*($C$2:$H$2=C10)*$C$3:$H$7)

注:利用=號對比把符合條件的變成True,不符合條件的變為false,再用(行數據)*(列數據)轉換成N行N列的數組並把不符合條件的全部變成0,而符合條件的會變成1,然後再*值區域把1轉換成符合條件的數值,最後用sumprouct提取值。

公式7

=SUM((B3:B7=B10)*(C2:H2=C10)*(C3:H7)) 數組公式

注:原理同公式6,只是這裡用了sum函數提取唯一符合條件(大於0)的值。因為sum函數不能直接進行數組運算,所以本公式為數組公式,要用ctrl+shift+enter完成輸入。

公式8

=MAX((B3:B7=B10)*(C2:H2=C10)*(C3:H7)) 數組公式

注:原理同公式7,因為是數組中除了符合條件的值外,其餘的全是0,所以這裡也可以用max求最大值的方式提取數值。本公式同樣也是數組公式,輸入方法同7.


分享到:


相關文章: