Microsoft Office Tutorials and References

In Depth Information

**Specialized Lookup Formulas**

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 formulas that look up the commission rate from the

appropriate table. For example, the formula in cell D2 is

=VLOOKUP(C2,IF(B2<3,CommTable1,CommTable2),2)

The second argument for the VLOOKUP function consists of an IF function 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 column 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 8-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.

Figure 8-10:
Looking up letter grades for test scores.

Column C contains formulas that use the VLOOKUP function and the lookup table to assign a

grade based on the score in column B. The formula in C2, for example, is

=VLOOKUP(B2,GradeList,2)

When the lookup table is small (as in the example shown in Figure 8-10), you can use a literal

array in place of the lookup table. The formula that follows, for example, returns a letter grade