Microsoft Office Tutorials and References

In Depth Information

**Functions Relevant to Lookups**

LOOKUP. The MATCH and INDEX functions are often used together to return a cell or relative cell

reference for a lookup value.

The examples in this section (plus the example in Figure 8-1) are available on the

companion CD-ROM. The filename is
basic lookup examples.xlsx
.

The VLOOKUP function

The VLOOKUP function looks up the value in the first column of the lookup table and returns the

corresponding value in a specified table column. The lookup table is arranged vertically. The

syntax for the VLOOKUP function is

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

The VLOOKUP functionâ€™s arguments are as follows:

h
lookup_value:
The value that you want to look up in the first column of the lookup table.

h
table_array:
The range that contains the lookup table.

h
col_index_num:
The column number within the table from which the matching value is

returned.

h
range_lookup:
Optional. If TRUE or omitted, an approximate match is returned. (If an

exact match is not found, the next largest value that is less than
lookup_value
is used.) If

FALSE, VLOOKUP searches for an exact match. If VLOOKUP cannot find an exact match,

the function returns #N/A.

If the
range_lookup
argument is TRUE or omitted, the first column of the lookup table

must be in ascending order. If
lookup_value
is smaller than the smallest value in the

first column of
table_array,
VLOOKUP returns #N/A. If the
range_lookup
argument is

FALSE, the first column of the lookup table need not be in ascending order. If an exact

match is not found, the function returns #N/A.

If the
lookup_value
argument is text (and the fourth argument, r
ange_lookup,
is

FALSE), you can include the wildcard characters * and ?. An asterisk matches any group

of characters, and a question mark matches any single character.

The classic example of a lookup formula involves an income tax rate schedule (see Figure 8-2).

The tax rate schedule shows the income tax rates for various income levels. The following

formula (in cell B3) returns the tax rate for the income in cell B2:

=VLOOKUP(B2,D2:F7,3)