Syntax
=OFFSET(reference, rows, cols, [height], [width])Arguments
| Argument | Required | Description |
|---|---|---|
reference | Required | The starting reference |
rows | Required | Rows to move down (negative moves up) |
cols | Required | Columns to move right (negative moves left) |
height | Optional | Height of the result in rows (default: same as reference) |
width | Optional | Width of the result in columns (default: same as reference) |
How to use OFFSET
OFFSET is volatile: it recalculates every time the workbook changes, so its result can differ each time.
OFFSET takes 3 required arguments and 2 optional arguments. Type =OFFSET( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=OFFSET(A1, 1, 1)Result Data in the sheet
| B | |
|---|---|
| 2 | 5 |
=ROWS(OFFSET(A1, 0, 0, 3, 2))Result =OFFSET(A1, -1, 0)Result Related lookup and reference functions
- INDEXReturns the value or reference at a given row and column of a range or array.
- INDIRECTReturns the reference written in a text value, such as "B3" or "Sheet2!A1:A10".
- LOOKUPFinds a value in a sorted row or column and returns the value at the same position in another row or column.
- MATCHReturns the position of a value in a row or column.
- ROWReturns the row number of a reference, or of the cell with the formula when left out.
- ROWSReturns the number of rows in a range or array.
- TRANSPOSESwaps the rows and columns of a range or array.
- VLOOKUPLooks for a value in the first column of a table and returns the value in the same row from another column.
- XLOOKUPSearches a range or array for a match and returns the matching item from a second range or array.