OFFSET
The OFFSET function returns an array of cells that is the specified number of rows and columns away from the specified base cell.
OFFSET(base, row-offset, column-offset, rows, columns)
base: The cell reference of the cell from which the offsets are measured.
row-offset: The number of rows from the base cell to the target cell. row-offset is a number value. 0 means the target cell is in the same row as the base cell. A negative number means the target is in a row above the base.
column-offset: The number of columns from the base cell to the target cell. column-offset is a number value. 0 means the target cell is in the same column as the base cell. A negative number means the target is in a column to the left of the base.
rows: An optional number value specifying the number of rows to return, starting with the offset location.
columns: An optional number value specifying the number of columns to return, starting with the offset location.
Examples |
|---|
Suppose that you entered 1 in cell D7, 2 in cell D8, 3 in cell D9, 4 in cell E7, 5 in cell E8 and 6 in cell E9. =OFFSET(D7, 0, 0, 3, 1) entered in cell B6 returns an error, because the 3 rows and 1 column returned (the collection D7:D9) does not have a single intersection with B6 (it has none). =OFFSET(D7, 0, 0, 3, 1) entered in cell D4 returns an error, because the 3 rows and 1 column returned (the collection D7:D9) does not have a single intersection with D4 (it has three). =OFFSET(D7, 0, 0, 3, 1) entered in cell B8 returns 2, because the 3 rows and 1 column returned (the collection D7:D9) has a single intersection with B8 (cell D8, which contains 2). =OFFSET(D7:D9, 0, 1, 3, 1) entered in cell B7 returns 4, because the 3 rows and 1 column returned (the collection E7:E9) has a single intersection with B7 (cell E7, which contains 4). The following examples do not use the assumed cell contents discussed in the prior examples. =OFFSET(A1, 5, 5) returns the value in cell F6, because this is the cell five columns to the right and five rows below cell A1. =OFFSET(G13, 0, -1) returns the value in the cell to the left of G13, the value in F13. =SUM(OFFSET(A7, 2, 3, 5, 5)) returns the sum of the values in cells D9 to H13, the five rows and five columns that begin two rows to the right of and three columns below cell A7. =SUM(OFFSET(INDIRECT(A1), 0, 0, A2, A3)) returns the sum of the number of rows and columns specified in A2 and A3, respectively, beginning from the base cell specified in A1. |