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
 
Search JabSto ::




Custom Search