Nesting MATCH Function Inside OFFSET Function For Dynamic Calculation

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


Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *