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