Microsoft Office Tutorials and References
In Depth Information
This technique is fine for situations that involve only a few choices. However, using nested IF functions can
quickly become complicated and unwieldy. The lookup techniques described in this chapter usually provide a
much better solution.
Basic Lookup Formulas
You can use Excel's basic lookup functions to search a column or row for a lookup value to return another value
as a result. Excel provides three basic lookup functions: HLOOKUP, VLOOKUP, and 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 at this
book's website. 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 correspond-
ing value in a specified table column. The lookup table is arranged vertically. The syntax for the VLOOKUP
The VLOOKUP function's arguments are as follows:
• lookup_value: The value that you want to look up in the first column of the lookup table.
• table_array: The range that contains the lookup table.
• col_index_num: The column number within the table from which the matching value is returned.
• 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.