Page 326 - Excel 2007 Bible

20_044039 ch15.qxp 11/21/06 11:07 AM Page 283
Creating Formulas That Look Up Values
The array formula that follows is in cell B2. This formula does a case-sensitive lookup in Range1 and returns
the corresponding value in Range2.
{=INDEX(Range2,MATCH(TRUE,EXACT(Value,Range1),0))}
The formula looks up the word DOG (uppercase) and returns 300. The following standard LOOKUP formula
(which is not case sensitive) returns 400:
=LOOKUP(Value,Range1,Range2)
When entering an array formula, remember to use Ctrl+Shift+Enter.
NOTE
NOTE
Choosing among multiple lookup tables
You can, of course, have any number of lookup tables in a worksheet. In some situations, your formula may
need to decide which lookup table to use. Figure 15.9 shows an example.
This workbook calculates sales commission and contains two lookup tables: G3:H9 (named CommTable1)
and J3:K8 (named CommTable2). The commission rate for a particular sales representative depends on two
factors: the sales rep’s years of service (column B) and the amount sold (column C). Column D contains for-
mulas that look up the commission rate from the appropriate table. For example, the formula in cell D2 is 15
=VLOOKUP(C2,IF(B2<3,CommTable1,CommTable2),2)
FIGURE 15.9
This worksheet demonstrates the use of multiple lookup tables.

The second argument for the VLOOKUP function consists of an IF formula that uses the value in column B
to determine which lookup table to use.
The formula in column E simply multiplies the sales amount in column C by the commission rate in col-
umn D. The formula in cell E2, for example, is
=C2*D2

Determining letter grades for test scores
A common use of a lookup table is to assign letter grades for test scores. Figure 15.10 shows a worksheet
with student test scores. The range E2:F6 (named GradeList) displays a lookup table used to assign a letter
grade to a test score.

283