OFFSET(reference, rows, cols [,height] [,width]) 
Returns the value in a cell which is an offset from another cell. 
reference  The reference from which you want to base the offset. 
rows  The number of rows to move from the reference cell (positive = down, negative = up). 
cols  The 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 

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