Lookup
OFFSET
Definition
OFFSET returns a cell shifted from a starting cell by a given number of rows and columns.
Syntax
=OFFSET(reference, rows, cols)
Arguments
| Argument | Meaning |
|---|---|
reference |
The starting cell, such as A2. |
rows |
How many rows to move. 0 stays on the start row; 1 moves down one row. |
cols |
How many columns to move. 0 stays in the start column. |
Worked example
A tally lists running totals in A2:A4, and you need the value one row below the start.
| A: Total |
|---|
| 5 |
| 8 |
| 13 |
Formula: =OFFSET(A2,1,0)
Result: 8. One row below A2 is A3, which holds 8.
Practice
Cells A2:A4 hold 10, 20, and 30. In B2, write a formula that returns the value two rows below A2.
- In B2, write an OFFSET formula that starts at A2 and moves 2 rows down and 0 columns right.
- B2 should show the value 2 rows down from A2.
Step 1 of 2
Enter =OFFSET(A2,2,0) in B2.
Two rows down from A2 is A4, which is 30.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses OFFSET.
B2 now shows 30.
Related functions
INDIRECT uses a text address instead of a shift. INDEX uses a position inside a range.
Common mistake
Counting the start cell as row 1 of the shift: OFFSET(A2,0,0) is A2 itself, so two rows below A2 is OFFSET(A2,2,0), not 3.