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
Search JabSto ::




Custom Search