Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to combine MATCH function with OFFSET Function
OFFSET Function returns a reference to a range that is a given number of rows and columns from a given reference
OFFSET Syntax =OFFSET(reference, rows, cols, [height], [width])
MATCH Function returns the relative position of an item in an array that matches a specified value in a specified order
MATCH Syntax =MATCH(lookup_value, lookup_array, [match_type])
In the caption below, we want to return the Total Sales for selected Account Manager’s name and Year.
- In cell D23, execute the formula: =OFFSET(A4,MATCH(B23,A5:A18,0),MATCH(B24,B4:J4,0))
A4 represent the reference argument of the OFFSET function
MATCH(B23,A5:A18,0) represent the rows argument of the OFFSET function
MATCH(B24,B4:J4,0) represent the cols argument of the OFFSET function
[height] and [width] arguments of the OFFSET function are optional.
- CTRL + Enter
From the caption above, we can see that the Total Sales generated by Ojo John for year 2015 is $5,555.00 which correspond with the value in yellow fill colour. Hope you enjoyed the tutorial