Page 318 - Excel 2007 Bible

20_044039 ch15.qxp 11/21/06 11:07 AM Page 275
Creating Formulas That Look Up Values
Description
Function
Returns a value either from a one-row or one-column range. Another form of the LOOKUP
LOOKUP
function works like VLOOKUP but is restricted to returning a value from the last column of a range.
Returns the relative position of an item in a range that matches a specified value.
MATCH
Returns a reference to a range that is a specified number of rows and columns from a cell or range
OFFSET
of cells.
Vertical lookup. Searches for a value in the first column of a table and returns a value in the same
VLOOKUP
row from a column you specify in the table.
* Available in Excel 2007 only.
The examples in this chapter use the functions listed in Table 15.1.
Basic Lookup Formulas
You can use the Excel basic lookup functions to search a column or row for a lookup value to return 15
another value as a result. Excel provides three basic lookup functions: HLOOKUP, VLOOKUP, and LOOKUP.
In addition, the MATCH and INDEX functions are often used together to return a cell or relative cell refer-
ence for a lookup value.
Using the IF Function for Simple Lookups
he IF function is very versatile and is often suitable for simple decision-making problems. The accompany-
Ting figure shows a worksheet with student grades in column B. Formulas in column C use the IF function to
return text: either Pass (a score of 65 or higher) or Fail (a score below 65). For example, the formula in cell C2 is
=IF(B2>=65,”Pass”,”Fail”)

You can “nest” IF functions to provide even more decision-making ability. This formula, for example, returns
one of four strings: Excellent, Very Good, Fair, or Poor.

=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. But using nested IF functions can quickly
become complicated and unwieldy. The lookup techniques described in this chapter provide a much better
solution.

275