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