Calc Guide

Table 23. Breakdown of Listing 19.
Function Description
MATCH("Bob";A1:A16; 0) Return 4 because Bob is the fourth entry in
column A.
ADDRESS(4; 4) Return $D$4.
INDIRECT("$D$4") Convert $D$4 into a reference to the cell D4.
OFFSET($D$4; 0; 0; 1; 2) Return the range D4:E4.
SUM(D4:E4) Return the sum of Bob’s quiz scores.
Although Listing 19 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 20 uses a slightly different method to obtain
Bob’s quiz scores.
Listing 20. Better use of OFFSET.
=SUM(OFFSET(A1; MATCH("Bob"; A1:A16; 0)-1; 3; 1; 2))
Table 24 contains a description of each function used in Listing 20. To
help convince yourself that Listing 20 is better than Listing 19, replace
A1 with A2 in both Listing 20 and Table 24 and notice that you still
obtain Bob’s quiz scores.
Table 24. Breakdown of Listing 20.
Function Description
MATCH("Bob";A1:A16; 0)-1 Return 3 because Bob is the fourth entry in
column A.
OFFSET(A1; 3; 3; 1; 2) Return the range D4:E4.
SUM(D4:E4) Return the sum of Bob’s quiz scores.
Tip
The first argument to OFFSET can be a range so you can use a
defined range name.
INDEX returns cells inside a specified range
INDEX returns the cells specified by a row and column number. The
row and column number are relative to the upper left corner of the
specified reference range. For example, using =INDEX(B2:D3; 1; 1)
Chapter 13 Calc as a Simple Database 385