OFFSET Function in Excel – Formula Explained with Examples

OFFSET Function: Explained with Examples

OFFSET formula is very useful to travel in the Worksheet and fetch a specific range from particular Cell or Range. You can use OFFSET function to lookup a value to the left side or above a range which is not possible with VLOOKUP or HLOOKUP Functions in Excel.

What is the use of OFFSET function?

OFFSET Function in Excel returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range

What is the syntax of OFFSET function?

OFFSET( reference, rows, cols,

reference : reference cell/range that is to be offset (can be either a single cell or multiple cells)
rows : number of rows from the start ing position (upper-left) of the supplied reference range (positive integers travels down, negetive integers travels up)
cols : number of column from the starting position (upper-left) of the supplied reference range (positive integers travels right from the refernce, negetive integers travels left from the reference
[height]: height of the returned range, if you omit this it will retrn the same height of the given range
[width]: width of the returned range, if you omit this it will retrn the same width of the given range

OFFSET Function in Excel – Examples

Example 1: OFFSET function returns 22 as output:
Initial supplied range is A15, it travels 1 row (down), 1 column (right). And selects one cell as the initial supplied range height is one cell. And =OFFSET(A15,1,1) formula returns 22 as output.

Example 2: OFFSET function returns 200 as output:

Initial supplied range is B29, from here it travels -2 row (up), 1 column (right). And selects one cell as the initial supplied range height is one cell. And =OFFSET(B29, -2,1) formula returns 200 as output.

Example 3: OFFSET function returns 12560 as output:
OFFSET: Initial supplied range is A40, from here it travels 2 row (down), 2 column (right). And it select the 4 rows and 1 column.

SUM: It consider the range returned from OFFSET function (i.e; C42:C45) and returns its SUM value 12560 as output.

Reference:

Please refer the below article for more Lookup & Reference Excel functions.
Lookup & Reference Excel Formulas

Please refer the below article for more Excel Functions.
Excel Formulas | Home