Microsoft Office Tutorials and References
In Depth Information
Specialized Lookup Formulas
Looking up a value to the left
The VLOOKUP function always looks up a value in the first column of the lookup range. But what
if you want to look up a value in a column other than the first column? It would be helpful if you
could supply a negative value for the third argument for VLOOKUP — but you can’t.
Figure 8-7 illustrates the problem. Suppose you want to look up the batting average (column B,
in a range named Averages ) of a player in column C (in a range named Players ). The player you
want data for appears in a cell named LookupValue. The VLOOKUP function won’t work because
the data is not arranged correctly. One option is to rearrange your data, but sometimes that’s not
Figure 8-7: The VLOOKUP function can’t look up a value in column B, based on a value in column C.
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 LookupValue:
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 to
using this formula is that the player names need not be sorted.