OFFSET

Syntax:

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

Description: Gets a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Arguments:

Name

Type

Description

reference

reference

Designates the base. reference shall refer to a cell or range of adjacent cells.

rows

number

The number of rows, up or down, that indicates the upper-left cell of the result to refer to. A positive value means below the starting reference; a negative value means above the starting reference.

cols

number

The number of columns, to the left or right, that the upper-left cell of the result to refer to. A positive value means to the right of the starting reference; a negative value means to the left of the starting reference.

height

number

The height, in rows, of the set of cells referred to by the resulting reference. This height shall be positive. If omitted, it is the same as the height of reference.

width

number

The width, in columns, of the set of cells referred to by the resulting reference. The width shall be positive. If omitted, it is the same as the width of reference.

Return Type and Value: reference – A reference to a range that is a specified size and number of rows and columns from a cell or range of cells.

However, if

  • reference does not refer to a cell or range of adjacent cells, #VALUE! is returned.

  • The combination of rows and cols results outside the worksheet, #REF! is returned.

example: