Microsoft Office Tutorials and References

In Depth Information

**Understanding lookup and reference functions**

parentheses.) For example, in the formula =INDEX((A1:C5,D6:F10), 1, 1, 2), the
reference

range comprises two areas: A1:C5 and D6:F10. The
area_num
argument (2) tells INDEX to

work on the second of these areas. This formula returns the address D6, which is the cell in

the first column and first row of the range D6:F10. The displayed result is the value in that

cell.

The INDIRECT function

The INDIRECT function returns the contents of a cell by using its reference. It takes the

arguments (
ref_text, a1
), where
ref_text
is an A1-style or R1C1-style reference or a cell name.

The
a1
argument is a logical value indicating which type of reference you’re using. If
a1

is FALSE, Excel interprets
ref_text
as R1C1 format; if
a1
is TRUE or omitted, Excel interprets

ref_text
as A1 format. For example, if cell C6 on your worksheet contains the text value
B3

and cell B3 contains the value
2.888
, the formula =INDIRECT(C6) returns the value
2.888
.

If your worksheet is set to display R1C1-style references and cell R6C3 contains the text

reference R3C2 and cell R3C2 contains the value
2.888
, then the formula =INDIRECT(R6C3,

FALSE) also returns the value
2.888
.

For information about A1-style and R1C1-style references, see “Understanding the

rowcolumn reference style” in Chapter 12.

The ROW and COLUMN functions

The result of the ROW and COLUMN functions is the row or column number, respectively,

of the cell or range referred to by the function’s single argument. For example, the formula

=ROW(H5) returns the result
5
. The formula =COLUMN(C5) returns the result
3
because

column C is the third column on the worksheet.

If you omit the argument, the result is the row or column number of the cell that contains

the function. If the argument is a range or a range name and you enter the function as an

array by pressing Ctrl+Shift+Enter, the result of the function is an array that consists of the

row or column numbers of each row or column in the range. For example, suppose you

select cells B1:B10, type the formula
=ROW(A1:A10)
, and then press Ctrl+Shift+Enter to

enter the formula in all cells in the range B1:B10. That range will contain the array result

{1;2;3;4;5;6;7;8;9;10}, the row numbers of each cell in the argument.

The ROWS and COLUMNS functions

The ROWS and COLUMNS functions return the number of rows or columns, respectively,

referenced by the function’s single argument in a reference or an array. The argument is an

array constant, a range reference, or a range name. For example, the result of the formula

=ROWS({100,200,300;1000,2000,3000}) is
2
because the array consists of two rows (sepa-

rated by a semicolon). The formula =ROWS(A1:A10) returns
10
because the range A1:A10