Excel Formula for Dynamic Ranges with Offset and Match

Any excel formula which takes the range of cells as an input, we feed the range of cells by selecting them directly. What if those references has to keep changing based on your requirement? Changing references inside the formula manually will not be effective method.

OFFSET is one of the formulas which will help you to handle it. It becomes more powerful when it combines with other functions like MATCH. In the below example, the price of all the four products are added by using SUM formula.

In the above example, what if we want to add the price of any 2 or 3 products of user choice . This would become a very difficult task if the number of products is too many.

To make our job easy, here comes the feature of referring Dynamic Ranges with Offset & Match formulas.

In the below example, we have sales values for a period of twelve months. Now our requirement is to add the values pertaining to few consecutive months like Jan to Nov or Mar to Sep. This calculation is possible using Offset & Match formulas referring the Dynamic Ranges.

Below is the original table:

Now input Start & End in a column next to the table as below:

Now we are going to input the First month in the cell next to the Start Cell & Last month in the next to the End Cell. That is, if we need to add the values of the months from Feb to Nov, First month here is Feb and Last month is Nov.

Position of the month names is needed here to make our formula to work. MATCH is the formula which will help you to find position of a value in a list. For Jan it is 1, for Feb it is 2 and so on) is derived next to the month by using Match Formula as below:

The above Match formula throws the result as 2 for Feb & 11 for Nov.

Syntax of Match formula:

=MATCH (lookup_value, lookup_array, [match_type])

lookup_value: here is Feb,

lookup_array: is the list where we must check for the position of month names. In this case select the cells from A2 to A13

match type: we need exact match position of the month names, hence entering the value as 0.

Have a look at the below table:

Syntax of Offset Formula:

=OFFSET(reference, rows, cols, [height],[width])

reference – From which cell the offset must take place (Here it is the value pertaining to the month of January)

rows – number of rows to be moved to get the start month (Here the number of rows to move is derived by subtracting 1 from the match result of month name position (2-1 = 1).)

cols – number of columns to be moved to get the start month (Here the values are present in the same column where we have our reference cell hence value will be 0)

height – total no of rows to be fed (Here the total is derived by adding 1 to the difference between the positions of starting and ending month names (Feb & Nov : 2 & 11: 11-2+1 = 10))

column – total no of columns to be involved to reach the end month (As only one column is involved, the total is 1)

The offset formula will now add only those values pertaining to the months from Feb to Nov is same as the value derived by adding the values in a usual way. Refer yellow highlighted cells.

Now we shall try for different dynamic ranges by changing the Start & End months

Hope this posting has helped you increasing your knowledge on Excel. Happy Learning!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.