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
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
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