Calc Guide

returns the cell B2. Table 25 lists shows the syntax for using the
INDEX function.
Table 25. Syntax for INDEX.
Syntax Description
INDEX(reference) Return the entire range.
INDEX(reference; row) Return the specified row in the range.
INDEX(reference; row;
column)
Return the cell specified by row and column. A
row and column of 1 returns the cell in the
upper left corner of the range.
INDEX(reference; row;
column; range)
A reference range can contain multiple ranges.
The range argument specifies which range to
use.
The INDEX function can return an entire range, a row, or a single
column (see Table 25). The ability to index based on the start of the
reference range provides some interesting uses. Using the values
shown in Table 12, Listing 21 finds and returns Bob’s quiz scores.
Table 26 contains a listing of each function used in Listing 21.
Listing 21. Return Bob’s quiz scores.
=SUM(OFFSET(INDEX(A2:G16; MATCH("Bob"; A2:A16; 0)); 0; 3; 1; 2))
Table 26. Breakdown of Listing 21.
Function Description
MATCH("Bob";A2:A16; 0) Return 3 because Bob is the third entry in
column A2:A16.
INDEX(A2:A16; 3) Return A4:G4—the row containing Bob’s quiz
scores.
OFFSET(A4:G4; 0; 3; 1; 2) Return the range D4:E4.
SUM(D4:E4) Return the sum of Bob’s quiz scores.
Tip
A simple range contains one contiguous rectangular region of
cells. It is possible to define a multi-range that contains multiple
simple ranges. If the reference consists of multiple ranges, you
must enclose the reference or range name in parentheses.
If reference argument to the INDEX function is a multi-range, then the
range argument specifies which simple range to use (see Table 27).
386 OpenOffice.org 3.x Calc Guide