Page 325 - Excel 2007 Bible

20_044039 ch15.qxp 11/21/06 11:07 AM Page 282
Part II
Working with Formulas and Functions
Using the VLOOKUP function requires that the lookup range (in this case, the Players range) is in ascending
order. In addition to this limitation, the formula suffers from a slight problem: If you enter a nonexistent
player (in other words, the LookupValue cell contains a value not found in the Players range), the formula
returns an erroneous result.
A better solution uses the INDEX and MATCH functions. The formula that follows works just like the previ-
ous one except that it returns #N/A if the player is not found. Another advantage is that the player names
need not be sorted.
=INDEX(Averages,MATCH(LookupValue,Players,0))
FIGURE 15.7
The VLOOKUP function can’t look up a value in column B, based on a value in column C.
Performing a case-sensitive lookup
The Excel lookup functions (LOOKUP, VLOOKUP, and HLOOKUP) are not case sensitive. For example, if
you write a lookup formula to look up the text budget, the formula considers any of the following a match:
BUDGET, Budget,or BuDgEt.
Figure 15.8 shows a simple example. Range D2:D7 is named Range1, and range E2:E7 is named Range2.
The word to be looked up appears in cell B1 (named Value).

FIGURE 15.8
Using an array formula to perform a case-sensitive lookup.

282