Microsoft Office Tutorials and References

In Depth Information

**Understanding lookup and reference functions**

The ADDRESS function

The ADDRESS function provides a handy way to build a cell reference by using numbers

typed into the formula or using values in referenced cells. It takes the arguments (
row_num,

column_num, abs_num, a1, sheet_text
). For example, the formula =ADDRESS(1, 1, 1, TRUE,

"Data Sheet") results in the reference 'Data Sheet'v!$A$1.

The CHOOSE function

You use the CHOOSE function to retrieve an item from a list of values. The function takes

the arguments (
index_num, value 1, value 2, …
) and can include up to 254 values. The

index_num
argument is the position in the list you want to return; it must be positive and

can’t exceed the number of elements in the list. The function returns the value of the

element in the list that occupies the position indicated by
index_num
. For example, the

formula =CHOOSE(2, 6, 1, 8, 9, 3) returns the value
1
, because
1
is the second item in the list.

(The
index_num
value isn’t counted as part of the list.) You can use individual cell references

for the list, but you can’t specify ranges. You might be tempted to create a formula such as

=CHOOSE(A10, C1:C5) to take the place of the longer function in the preceding example. If

you do, however, the result is a #VALUE! error value.

The MATCH function

The MATCH function is closely related to the CHOOSE function. However, where CHOOSE

returns the item that occupies the position in a list specified by the
index_num
argument,

MATCH returns the position of the item in the list that most closely matches a lookup value.

Note

You can create powerful lookup formulas by using the MATCH and INDEX functions.

See “Using lookup functions” in Chapter 12.

This function takes the arguments (
lookup_value, lookup_array, match_type
), where
lookup_

value
and the items in the
lookup_array
can be numeric values or text strings, and
match_

type
defines the rules for the search, as shown in Table 14-4.

When you use MATCH to locate text strings, you should specify a
match_type
argument of

0 (an exact match). You can then use the wildcards * and ? in the
lookup_value
argument.