Microsoft Office Tutorials and References

In Depth Information

Another solution is to use the LOOKUP function, which requires two range arguments. The following formula

(in cell F3) returns the batting average from column B of the player name contained in the cell named
Lookup-

Value:

=LOOKUP(LookupValue,Players,Averages)

Using the LOOKUP function requires that the lookup range (in this case, the
Players
range) is in ascending or-

der. 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 to using this formula is that the

player names don't need to be sorted.

=INDEX(Averages,MATCH(LookupValue,Players,0))

Performing a case-sensitive lookup

Excel's 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 8-8 shows a simple example. Range D2:D9 is named
Range1,
and range E2:E9 is named
Range2.
The

word to be looked up appears in cell B1 (named
Value
).