Return a reference that is a specified number of rows and columns from a starting point

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

---

---

OFFSET defines 'a starting point', 'a row offset', 'a column offset', 'a height in rows' and 'a width in columns'.

For example, =OFFSET(B2,2,2,1,1) will return a single reference 2 rows and 2 columns away from the starting point cell B2 (i.e. cell D4).

OFFSET can return a single value or a range of values, determined by the height and width arguments.

OFFSET allows formulas to dynamically adjust to available data so is useful for dynamic named ranges.

---

OFFSET and single offsets.

In the examples below, the OFFSET function is used to return population data for specific countries. Note the use of the MATCH function in the rows and cols arguments which can help create a dynamic formula.

---

OFFSET and multiple offsets.

In the examples below, the OFFSET function is used to return both horizontal and vertical arrays of population data for specific regions or countries.

---

Syntax

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

The OFFSET function consists of at least 3 arguments:

1. reference. Required. The starting point from which to base to offset. reference must refer to a cell or a range of adjecent cells, otherwise the #VALUE! error is returned.
2. rows. Required. The number of rows (up or down) that determine the upper-left cell of the range to be returned. rows can be positive (result is below the starting point) or negative (result is above the starting point).
3. cols. Required. The number of columns (left or right) that determine the upper-left cell of the range to be returned. cols can be positive (result is to the right of the starting point) or negative (result is to the left of the starting point).
4. height. Optional. The number of rows to be returned. Must be a positive number.
5. width. Optional. The number of columns to be returned. Must be a positive number.

---

Try it now!

• Enter your formulas into the grey cells.

---

Scope

OFFSET defines 'a starting point', 'a row offset', 'a column offset', 'a height in rows' and 'a width in columns'.

Single value:	=OFFSET(B2,2,2,1,1) || result is a single value from cell D4 which is 2 rows down and 2 columns across from the starting point cell B2.
Multiple values (columns):	=OFFSET(B2,2,1,1,2) || result is 2 values from cells C4 & D4 which are 2 rows down and 1 column across from the starting point cell B2.
Multiple values (rows):	=OFFSET(B2,6,4,2,1) || result is 2 values from cells F8 & F9 which are 6 rows down and 4 columns across from the starting point cell B2.
OFFSET with SUM:	=SUM(OFFSET(B2,1,2,5,1)) || result is sum of values in range D3:D7 which is 1 row down and 2 columns to the right from the starting point cell B2 and has a height of 5 rows. This would be the same as =SUM(D3:D7).

Caveats

• OFFSET is a volatile function, recalculating with every worksheet change, so can make larger or more complex workbooks run slower.
• reference
  • Can be one cell or a range of cells.
• rows
  • If rows references over the edge of the worksheet, the #REF! error is returned.
• cols
  • If cols references over the edge of the worksheet, the #REF! error is returned.
• height
  • Assumed to be the same height as reference if omitted.
  • Shouldn't be negative according to Excel documentation but seems to work nevertheless.
• width
  • Assumed to be the same width as reference if omitted.
  • Shouldn't be negative according to Excel documentation but seems to work nevertheless.

---

Common issues

• #VALUE! error is returned when reference is neither a cell or a range of adjacent cells.
• #REF! error is returned if the rows and cols reference over the edge of the worksheet.

---

Related Functions

• INDEX and MATCH to lookup values in any direction.

---

Functions Category

• Lookup & Reference

 

---