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

possible.

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:

=LOOKUP(LookupValue,Players,Averages)

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.

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