Microsoft KB Archive/324991

= How to use the OFFSET function in Excel =

Article ID: 324991

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



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.

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 &quot;101&quot;. Therefore, the formula returns &quot;101&quot;.

Additional query words: inf XL2002

Keywords: kbhowto KB324991

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.