The Excel OFFSET function returns the value of a cell at a specified row and column offset from a reference cell. When working with ranges that change based on conditions, it lets you continuously retrieve values from the desired position regardless of how the range moves.
Excel OFFSET Function Syntax
The OFFSET function syntax is as follows.
=OFFSET (reference cell, row offset, column offset)
How to Find a Value with Excel OFFSET
Using the sales data below, we will use the OFFSET function to return a value from the desired position.
The function syntax for finding a value by position is as follows.
- Reference cell: B2 cell, Seller name
- Row offset: 3
- Column offset: 2
G6 cell = OFFSET (B2, 3, 2)
Keep the following in mind when using the function.
When specifying row and column offsets as numbers, count the positions starting from 0.
How to Set a Dynamic Range with Excel OFFSET
The OFFSET function syntax for setting a dynamic range is as follows.
=OFFSET(reference cell, row offset, column offset, range height, range width)
As shown in the data above, the OFFSET function sets a range that moves relative to the reference cell.
- Reference cell: B2 cell, Seller name
- Row offset: 3
- Column offset: 2
- Height: 3
- Width: 1
G8 cell = OFFSET(B2,3,2,3,1)
The range returned by the formula above is the same as D5:D7.
How to Use OFFSET with Other Excel Functions
We have covered two features: finding a value by position and setting a dynamic range. Next, let’s look at how to use OFFSET with other functions.
We have covered two features: finding a value by position and setting a dynamic range. Next, let’s look at how to use OFFSET with other functions.
SUM & OFFSET
SUM uses the dynamic range returned by OFFSET to calculate a total.
The formula used is as follows.
G11 cell = SUM (OFFSET(B2,3,2,3,1))
MAX MIN & OFFSET
MAX uses the dynamic range returned by OFFSET to calculate the maximum value.
The formula used is as follows.
G11 cell = MAX (OFFSET(B2,3,2,3,1))
INDEX & OFFSET
INDEX uses the dynamic range returned by OFFSET to return the value from the desired position.
The formula used is as follows.
G11 cell = INDEX (OFFSET(B2,3,2,3,1),2,1)
Conclusion
We have covered the Excel OFFSET function. Remember its basic definition and syntax. Once you can use OFFSET with functions that return a desired value from a specific position, such as INDEX MATCH, you can consider yourself to have mastered it. To use the formulas continuously, learn about absolute references.