OFFSET returns a cell or range offset from another
Use OFFSET to return a cell or range offset a specified number of rows and columns from a given reference point. The first argument, specifies the reference point. The second and third arguments specify the number of rows and columns to move from the reference point; in other words, where the new range starts. The OFFSET function has the following syntax:
OFFSET(reference; rows; columns)
OFFSET(reference; rows; columns; height)
OFFSET(reference; rows; columns; height; width)
 | If the width or height is included, the OFFSET function returns a range. If both the width and height are missing, a cell reference is returned.
|
If the height or width are missing, they default to 1. If the height is present, then a range reference is returned rather than a cell reference. Using values from Table 1, Listing 10 uses OFFSET to obtain the quiz scores for the student named Bob.
Listing 10. Complex example of OFFSET.
=SUM(OFFSET(INDIRECT(ADDRESS(MATCH("Bob";A1:A16; 0); 4)); 0; 0; 1; 2))
In its entirety, Listing 10 is complex and difficult to understand. Table 12 isolates each function in Listing 10, providing an easy to understand explanation of how the example works.
Table 12. Breakdown of Listing 10.
| Function
| Description
|
| MATCH("Bob";A1:A16; 0)
| Returns 4 because Bob is the fourth entry in column A.
|
| ADDRESS(4; 4)
| Returns $D$4.
|
| INDIRECT("$D$4")
| Converts $D$4 into a reference to the cell D4.
|
| OFFSET($D$4; 0; 0; 1; 2)
| Returns the range D4:E4.
|
| SUM(D4:E4)
| Returns the sum of Bob’s quiz scores.
|
Although Listing 10 works as intended, it breaks easily and unexpectedly. Consider, for example, what happens if the range is changed to A2:A16. MATCH returns an offset into the provided range, so MATCH("Bob";A2:A16 ; 0) returns 3 rather than 4. ADDRESS(3; 4) returns $D$3 rather than $D$4 and Betty’s quiz scores are returned instead of Bob’s. Listing 11 uses a slightly different method to obtain Bob’s quiz scores.
Listing 11. Better use of OFFSET.
=SUM(OFFSET(A1; MATCH("Bob"; A1:A16; 0)-1; 3; 1; 2))
Table 13 contains a description of each function used in Listing 11. To help convince yourself that Listing 11 is better than Listing 10, replace A1 with A2 in both Listing 11 and Table 13 and notice that you still obtains Bob’s quiz scores.
Table 13. Breakdown of Listing 11.
| Function
| Description
|
| MATCH("Bob";A1:A16; 0)-1
| Returns 3 because Bob is the fourth entry in column A.
|
| OFFSET(A1; 3; 3; 1; 2)
| Returns the range D4:E4.
|
| SUM(D4:E4)
| Returns the sum of Bob’s quiz scores.
|
|
| The first argument to OFFSET can be a range so you can use a defined range name.
|
