Microsoft Office Tutorials and References

In Depth Information

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, Table1, Table2),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