Excel 2010 Bible

Part II: Working with Formulas and Functions

320

Using the LOOKUP 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

previous 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 14.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 14.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 14.8

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