Microsoft Office Tutorials and References
In Depth Information
Understanding lookup and reference functions
TABLE 14-4 MATCH function arguments
match_type Description
1 (or omitted) Finds the largest value in the specified range (which must be sorted in
ascending order) that is less than or equal to lookup_value . If no items in
the range meet these criteria, the function returns #N/A.
Finds the first value in the specified range (no sorting necessary) that
is equal to lookup_value . If no items in the range match, the function
returns #N/A.
0
Finds the smallest value in the specified range (which must be sorted in
descending order) that is greater than or equal to lookup_value . If no
items in the range meet these criteria, the function returns #N/A.
–1
The INDEX function
The INDEX function has two forms: an array form, which returns a value, and a reference
form, which returns a cell reference. The forms of these functions are as follows:
=INDEX(array, row_num, column_num)
=INDEX(reference, row_num, column_num, area_num)
The array form works only with an array argument; it returns the value of the result, not
the cell reference. The result is the value at the position in array indicated by row_num and
column_num . For example, the formula
=INDEX({10,20,30;40,50,60} , 1, 2)
returns the value 20 , because 20 is the value in the cell in the second column and first row
of the array.
Note
Each form of the INDEX function offers an advantageous feature. Using the reference
form of the function, you can use multiple, nonadjacent areas of the worksheet as the
reference lookup range. Using the array form of the function, you can get a range of
cells, rather than a single cell, as a result.
The reference form returns a cell address instead of a value and is useful when you want
to perform operations on a cell (such as changing the cell’s width) rather than on its value.
This function can be confusing, however, because if an INDEX function is nested in another
function, that function can use the value in the cell whose address is returned by INDEX.
Furthermore, the reference form of INDEX doesn’t display its result as an address; it displays
Search JabSto ::




Custom Search