How to use the OFFSET function in Excel 2003 and in Excel 2002 (324991)


The information in this article applies to:

  • Microsoft Excel 2003
  • Microsoft Excel 2002
This article was previously published under Q324991

SUMMARY

This article describes how to use the 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.

MORE INFORMATION

Enter the following data into a blank Excel worksheet. You will use this data for all sample formulas in this article.
A1: NameB1: DeptC1: Age
A2: HenryB2: 501C2: 28
A3: StanB3: 201C3: 19
A4: MaryB4: 101C4: 22
A5: LarryB5: 301C5: 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 left, and negative numbers mean move to the right.
  • 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.

Examples:

When you use this formula, the OFFSET function returns the value of the cell that is located two rows down (2) and 1 row to the left (-1) of cell C2 (which is cell B4). The value in cell B4 is "101". Therefore, the formula returns "101".
Modification Type:MajorLast Reviewed:10/10/2006
Keywords:kbhowto KB324991
©2004 Microsoft Corporation. All rights reserved.