OFFSET function to return the value of a cell that is a specified number of rows and columns away from a cell or range of cells that you referenced in an adjacent range.
Enter the following data into a blank Excel worksheet. You will use this data for all sample formulas in this article.
A1: Name B1: Dept C1: Age
A2: Henry B2: 501 C2: 28
A3: Stan B3: 201 C3: 19
A4: Mary B4: 101 C4: 22
A5: Larry B5: 301 C5: 29
Enter the following formulas into cell E2 (or any available blank cell):
=OFFSET(C2,2,-1,1,1)
C2 - The referenced cell.
2 - Indicates the number of rows to move. Positive numbers mean move down, and negative numbers mean move up.
-1 - Indicates the number of columns to move. Positive numbers mean move to the right, and negative numbers mean move to the left.
1 (second last value) - (Optional.) Indicates how many rows of data to return. This number must be a positive number.
1 (last value) - (Optional.) Indicates how many columns of data to return. This number must be a positive number.
