OFFSET function

OFFSET returns the value of a cell the location of which is offset by a certain number of rows and columns.

Syntax

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

  • reference is the cell from which the function searches for the new reference.
  • rows is the number of cells by which the reference was corrected up (negative value) or down.
  • cols is the number of columns by which the reference was corrected to the left (negative value) or to the right.
  • height (optional) is the vertical height for an area that starts at the new reference position.
  • width (optional) is the horizontal width for an area that starts at the new reference position.

Example

=OFFSET(A1,2,2) returns the value in cell C3 (A1 moved by two rows down and two columns right). If C3 contains the value 100, this function returns the value 100.

=SUM(OFFSET(A1,2,2,5,6)) determines the total of the area that starts in cell C3. This area has a height of five rows and a width of six columns (area=C3:H7).