ADDRESS, INDIRECT, OFFSET, INDEX
Contents
ADDRESS returns a string with a cell’s address
Use ADDRESS to return a text representation of a cell address based on the row, column, and sheet; ADDRESS is frequently used with MATCH. The supported forms for ADDRESS are as follows:
ADDRESS(row; column) ADDRESS(row; column; abs) ADDRESS(row; column; abs; sheet)
The row and column are integer values where ADDRESS(1; 1) returns $A$1. The abs argument specifies which portion is considered absolute and which portion is considered relative (see Table 10); an absolute address is specified using the $ character. The sheet is included as part of the address only if the sheet argument is used. The sheet argument is treated as a string. Using ADDRESS(MATCH("Bob";A1:A5 ; 0); 2) with the data in Table 9 on the previous page returns $B$2.
 |
Calc supports numerous powerful functions that are not discussed here. For example, the ROW, COLUMN, ROWS, and COLUMNS statements are not discussed; a curious person would investigate these functions. |
Table 10. Values supported by the abs argument to ADDRESS.
| Value | Description |
|---|---|
| 1 | Use absolute addressing. This is the default value if the argument is missing or an invalid value is used. ADDRESS(2; 5; 1) returns $E$2. |
| 2 | Use an absolute row reference and a relative column reference. ADDRESS(2; 5; 2; "Blah") returns Blah.E$2. |
| 3 | Use a relative row reference and an absolute column reference. ADDRESS(2; 5; 3) returns $E2. |
| 4 | Use relative addressing. ADDRESS(2; 5; 4) returns E2. |
INDIRECT converts a string to a cell or range
Use INDIRECT to convert a string representation of a cell or range address to a reference to the cell or range. Table 11 contains examples accessing data as shown in table 9.
Table 11. Examples using INDIRECT.
| Example | Comment |
|---|---|
| INDIRECT("A2") | Return cell A2, which contains Bob. |
| INDIRECT(G1) | If Cell G1 contains the text A2, then this returns Bob. |
| SUM(INDIRECT("B1:B5")) | Return the sum of the range B1:B5, which is 194. |
| INDIRECT(ADDRESS(2; 1)) | Returns the contents of cell $A$2, which is Bob. |
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. |
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) returns the cell B2. Table 14 lists shows the syntax for using the INDEX function.
Table 14. Syntax for INDEX.
| Syntax | Description |
|---|---|
| INDEX(reference) | Return the entire range. |
| INDEX(reference; row) | Returns the specified row in the range. |
| INDEX(reference; row; column) | Returns 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 14). The ability to index based on the start of the reference range provides some interesting uses. Using the values shown in Table 1, Listing 12 finds and returns Bob’s quiz scores. Table 15 contains a listing of each function used in Listing 12.
Listing 12. Return Bob’s quiz scores.
=SUM(OFFSET(INDEX(A2:G16; MATCH("Bob"; A2:A16; 0)); 0; 3; 1; 2))
Table 15. Breakdown of Listing 12.
| Function | Description |
|---|---|
| MATCH("Bob";A2:A16; 0) | Returns 3 because Bob is the third entry in column A2:A16. |
| INDEX(A2:A16; 3) | Returns the A4:G4—the row containing Bob’s quiz scores. |
| OFFSET(A4:G4; 0; 3; 1; 2) | Returns the range D4:E4. |
| SUM(D4:E4) | Returns the sum of Bob’s quiz scores. |
|
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 16).
Table 16. Using INDEX with a multi-range.
| Function | Returns |
|---|---|
| =INDEX(B2:G2; 1; 2) | 93 |
| =INDEX(B5:G5; 1; 2) | 65 |
| =INDEX((B2:G2;B5:G5); 1; 2) | 93 |
| =INDEX((B2:G2;B5:G5); 1; 2; 1) | 93 |
| =INDEX((B2:G2;B5:G5); 1; 2; 2) | 65 |
| Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |
