OFFSET

Returns value from a data as per given offset range in Excel
Returns a reference offset from a given reference

Rational

Returns value from a data as per given offset range.

Description

The Excel OFFSET function returns one or multiple data points of a data set by providing a reference point of data set with a specified number of rows and columns to offset.
OFFSET is another function that can club with other functions as part of a more complicated formula.

Syntax

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

Parameters

reference – A cell address or range address but not named ranges.
row – Number of positive or negative rows to offset
cols – Number of positive or negative column to offset
height [optional]Value of number of rows to return.
width [optional]Value of number of columns to return.

Return Value

Cell reference based on the arguments

Key Notes

The OFFSET function returns a specified data point that is offset by a certain number of rows and columns from a reference cell.

Example:
The formula = SUM((OFFSET(B4,2,1,1,4)) states B4 as a starting point (reference) move 2 rows below to B6 (rows), 1 column right (cols) to C6 and 1 row [height] do not change position and cover 4 rows below i.e. C6:F6 [width]. So, the return value is summing all number in C6:F6.

If you keep [height] and [width] empty the OFFSET return an individual cell value and for [height] and [width] considered same as cell range (reference).
The Offset function can also be used as an array formula.

Keep Reading Similar Functions:

Returns a reference indicated by a text value

INDIRECT

The INDIRECT function creates a cell reference from a given text string same as the name of a worksheet and extracts value from a specific cell.

Read More »
Return a cell value as per row‐and‐column intersection in Excel

INDEX

The INDEX function returns a cell value from one or multiple data range at a given row‐and‐column intersection and gets a specific value from a data set.

Read More »
Return address of a cell in Excel

ADDRESS

The ADDRESS function returns cell address from given row and column numbers of a worksheet. For example, if you pass ADDRESS(5,6) the function returns $F$5.

Read More »