Microsoft Office Tutorials and References

In Depth Information

=IF(B2>=90,”Excellent”,IF(B2>=70,”Very

Good”,IF(B2>=50,”Fair”,”Poor”)))

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

function is

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

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.