Finding Data with the OFFSET Function
Excel OFFSET Function
© Ted French
OFFSET Function Overview
The OFFSET function can be used to refer to the location of specific data in an Excel worksheet.
The data can be located in a single cell or a range of cells.
Like many other Excel functions, on its own OFFSET has limited value. It is most effective when combined with other functions to create more complex formulas.
OFFSET can be used with a number of functions that accept a cell reference as an argument, such as SUM, COUNT, and MATCH.
If used on its own, as in the example below, the function returns the data located in the end point cell reference.
The OFFSET Function Syntax and Arguments
A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.
The syntax for the OFFSET function is:
= OFFSET (Reference, Rows, Cols, Height, Width)
Reference - (required) the starting point for the function. From this cell reference the function calculates the desired offset or end point located so many rows and columns away. The Reference argument can refer to a single cell or to range of adjacent cells
Rows - (required) the number of rows above or below the Reference argument used in calculating the offset. This value can be positive, negative, or set to zero
Cols - (required) the number of columns to the left or right of the Reference argument used in calculating the offset. This value can be positive, negative, or set to zero
Height - (optional) sets the height, in number of rows, of the returned offset. This value must be positive
Width - (optional) sets the width, in number of columns, of the returned offset. This value must be positive
- The Reference argument must be a cell reference to a single cell or range of adjacent cells; if not, OFFSET returns the #VALUE! error value
- The Rows and Cols arguments can be positive or negative in value - depending upon the location the offset (end point cell reference) desired. If the end point cell reference is to be located:
- below the start point cell reference in the worksheet - the Rows argument is positive
- above the start point - the Rows argument is negative
- to the right of the start point in the worksheet - the Cols argument is positive
- to the left of the start point - the Cols argument is negative
- If the Rows and/or Cols arguments are too large and, as a result, point to a location outside of the worksheet, OFFSET returns the #REF! error value
- If the Height and/or Width argument is omitted, OFFSET assumes these arguments to be the same height and/or width as the Reference argument
Example Using Excel's OFFSET Function to Find Data
As can be seen in the image above, the example uses the OFFSET function to return the name of the part listed third in column F of the worksheet.
For the purpose of simplicity, this example uses the cell where the OFFSET function is located - cell D3 - for the function's Reference argument.
Entering the Tutorial Data
- Enter the following data into cells D1 to F3
D2 - Part Name
F2 - Parts List
F3 - Widgets
F4 - Bearings
F5 - Cogs
Entering the OFFSET Function
- Click on cell D3 - this is where the results of this example will be displayed
- Click on the Formulas tab of the ribbon menu
- Choose Lookup and Reference from the ribbon to open the function drop down list
- Click on OFFSET in the list to bring up the function's dialog box
- In the dialog box, click on the Reference line
- Click on cell D3 in the worksheet to enter the cell reference into the dialog box as the Reference argument
- Click on the Rows line in the dialog box
- Enter the number " 2 " (no quotes) on this line since we are looking for data located two rows below cell D3
- Click on the Cols line in the dialog box
- Enter the number " 2" (no quotes) on this line since we are looking for data located two rows to the right of cell D3
- Click on the Height line in the dialog box
- Enter the number " 1 " (no quotes) on this line to specify that the ending cell reference is only one row in height
- Click on the Width line in the dialog box
- Enter the number " 1 " (no quotes) on this line to specify that the ending cell reference is only one column in width
- Click OK to complete the function and close the dialog box
- The word Cogs appears in cell D3 since it is the part name located two rows below and two columns to the right of the starting cell reference - cell D3
- When you click on cell D3 the complete function =OFFSET(D3,2,2,1,1) appears in the formula bar above the worksheet
Note: Since the end point cell reference was the same size - in terms of number of rows and columns - as the Reference argument - cell D3 - the Height and Width arguments could have been omitted in this example.
In doing so, the function would use the height and width of the Reference argument for the missing arguments.