Offset excel function: Formula, Examples, and Free video

Offset excel function

Excel offset function is a very interesting function that helps in getting data based on four parameters. These parameters are height, width, rows, and columns. Where VLOOKUP and HLOOKUP can get value from one side only, OFFSET function in excel can get value from any side. There is a lot of flexibility with OFFSET in excel as compared to other lookup functions in excel.

OFFSET function syntax

Let us understand how OFFSET excel function works. The OFFSET function syntax is given below.

In the above image we can see the inputs, reference means the reference from which the base is to be offset. It can be a cell or a range of adjacent cells. rows rows to move from starting point; can be positive (below starting reference) or negative (above starting reference). cols Columns to move from starting point; can be positive (right of starting reference) or negative (left of starting reference), [height] is a number that represents the number of rows in the returned reference and [width]  is a number that represents the number of columns in the returned reference.

Now let us understand this by some offset examples.

Example_1 From the below data we will try to find value for March Week_3.

In the above example_1 we took reference as C3 i.e. header name Month as wanted to pick value after that row. Now as the value is dynamic we will use the MATCH function to find out the position of March, after that column name we will again use the MATCH function to find out the column position of Week_3. We will skip height and width as we will be taking values below the reference.

Example_2 in this example we wanted to find value of Sum_Month for the month of May.

This is a case of reverse lookup. let us understand this be the below illustration.

In the above example we use the same reference C3 and for rows position we will use EXCEL MATCH function and for columns will you -1 as we are trying to look value from one column back.

Example_3 in this example we will try to find SUM of Week_1 data.

In the above offset examples, use used the same reference C3, as there is no particular row required so we put value 0 for rows, as they’re just one column for which we are calculating sum so putting cols as 1. Now as we wanted to sum of all values from the columns so we will put 13 in [height] and again there as there is just one column so [width] will be 1. And in the last, we will sum this.

Example-4 in this example we will try to learn about average for a row.

So we are trying to get average for the month of March.

In this example, we took the same reference C3, for rows we took input value 3 as march falls 3 rows below the reference. We can also use the MATCH function to find the position of the row. For column, we will put cols as 1. [height] we wouldn’t put any value as we are calculating values of rows only. In [width] we will put values 4 as we are trying to find the average of week_1 to week_4. After all this, we will wrap it with the excel AVERAGE function.

That’s all for now, will meet again next time with new interesting topic.

You also can read the below article for more information.
https://www.contextures.com/exceloffsetfunction.html
https://trumpexcel.com/excel-offset-function/
https://www.excel-easy.com/examples/offset.html

Don’t miss to watch the video to get comprehensive knowledge.