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.
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.