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 = up, negative = down).
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 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.office.com

 ABC
1=OFFSET(B1,1,0,1,1) = 428
2=OFFSET(B1,1,1,1,1) = 10410
3=OFFSET(C1,2,-1,1,1) = 6612
4=OFFSET(C3,-2,-1,1,1) = 2  
5=OFFSET(C2,0,0) = 10  
6=OFFSET(B1,1,0,3,3) = #VALUE!  


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