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