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-
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
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.
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 ).