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

Returns the value in a cell which is an offset from another cell.

referenceThe reference from which you want to base the offset.
rowsThe number of rows to move from the reference cell (positive = down, negative = up).
colsThe number of columns to move from the reference cell (positive = right, negative = left).
height(Optional) The number of rows in the returned range.
width(Optional) The number of columns in the returned range.

Remarks

* For an illustrated example refer to the page under Lookup Functions.
* This function can return a Dynamic Array Formula.
* This function is Volatile and will change everytime a cell on the worksheet is calculated.
* This function will update when the workbook is recalculated by pressing F9.
* This function can return a single value or multiple values.
* This function can return multiple values when it is entered as an Array Formula using (Ctrl + Shift + Enter).
* The "reference" must be a reference to a cell or range of adjacent cells.
* If "reference" is not a range of adjacent cells, then #VALUE! is returned.
* If "rows" > 0, then the offset will be below the "reference".
* If "rows" < 0, then the offset will be above the "reference".
* If "rows" is not an integer, it is truncated.
* If "rows" is not a number, then #VALUE! is returned.
* If "cols" > 0, then the offset will be to the right of the "reference".
* If "cols" < 0, then the offset will be to the left of the "reference".
* If "cols" is not an integer, it is truncated.
* If "cols" is not a number, then #VALUE! is returned.
* If "height" is left blank, then the height of the "reference" is used.
* If "height" > 0, then the returned range will be to the right of the "reference" range.
* If "height" < 0, then the returned range will be to the left of the "reference" range.
* If "height" is not an integer, it is truncated.
* If "height" is not a number, then #VALUE! is returned.
* If "width" is left blank, then the width of the "reference" is used.
* If "width" > 0, then the returned range will be below the "reference" range.
* If "width" < 0, then the returned range will be above the "reference" range.
* If "width" is not an integer, it is truncated.
* If "width" is not a number, then #VALUE! is returned.
* If the offset is off the worksheet, then #REF! is returned.
* This Excel function cannot be called from VBA and there is no VBA equivalent.
* You can use the MATCH function to return the position of a value.
* You can use the VLOOKUP function to return a value in the same row.
* You can use the HLOOKUP function to return a value in the same column.
* You can use the CHOOSE function to return the value in a list based on an index number.
* In Excel 2010 the rounding applied to "rows" and "cols" when values are not integers was changed. more
* For the Microsoft documentation refer to support.microsoft.com

 ABC
1=OFFSET(B1,1,0,1,1) = 428
2=OFFSET(B1,1,1,1,1) = 10410
3=OFFSET(C2,0,-1,1,1) = 4612
4=OFFSET(C2,-1,-1,1,1) = 2  
5=OFFSET(C2,0,0) = 10  
6=@OFFSET(B1,0,0,1,1) = 2  
7=@OFFSET(B1,0,0,2,2) = #VALUE!  
8=OFFSET(B1,1,0,3,3) = #SPILL!  
9=OFFSET(B1,-10,-10) = #REF!  

1 - What is the value in the cell when we offset from "B1" by rows (1) and columns (0).
2 - What is the value in the cell when we offset from "B1" by rows (1) and columns (1).
3 - What is the value in the cell when we offset from "C2" by rows (0) and columns (-1).
4 - What is the value in the cell when we offset from "C2" by rows (-1) and columns (-1).
5 - What is the value in the cell when we offset from "C2" by rows (0) and columns (0).
6 - What happens if the function includes the Implicit Intersection Operator @ and tries to return a single value.
7 - What happens if the function includes the Implicit Intersection Operator @ and tries to return multiple values.
8 - What happens if there is not enough space in one or more adjacent cells and the result is unable to spill over into the adjacent cells.
9 - What happens when the relative position goes off the worksheet.

© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited Top