OFFSET

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 multiple values.
* This is a Volatile function and will change everytime a cell on the worksheet is calculated.
* This function will update when the workbook is recalculated by pressing F9.
* 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 "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 "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 "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 any of the arguments are not integers, they are truncated.
* If any of the arguments are not numbers, 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 INDEX function to return the intersection of a row and a column.
* 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.
* You can use the OFFSET and MATCH combination to lookup a value in any column.
* In Excel 2010 the rounding applied to "rows" and "cols" when values are not integers was changed. more
* This function has been available since Excel 1997.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C
1	=OFFSET(B1, 1, 0, 1, 1) = 4	2	8
2	=OFFSET(B1, 1, 1, 1, 1) = 10	4	10
3	=OFFSET(B1, 0, 0, 1, 1) = 2	6	12
4	=OFFSET(C2, 0, -1, 1, 1) = 4
5	=OFFSET(C2, -1, -1, 1, 1) = 2
6	=OFFSET(C2, 0, 0) = 10
7	=OFFSET(B1, 0, 0, 1, 1) = 2
8	=OFFSET(B1,0,0,2,2) = #VALUE!
9	=OFFSET(B1, 1, 0, 3, 3) = #SPILL!
10	=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 "B1" by rows (0) and columns (0).
4 - What is the value in the cell when we offset from "C2" by rows (0) and columns (-1).
5 - What is the value in the cell when we offset from "C2" by rows (-1) and columns (-1).
6 - What is the value in the cell when we offset from "C2" by rows (0) and columns (0).
7 - What happens if the function includes the Implicit Intersection Operator @ and tries to return a single value.
8 - What happens if the function includes the Implicit Intersection Operator @ and tries to return multiple values.
9 - 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.
10 - What happens when the relative position goes off the worksheet.