Microsoft Office Tutorials and References
In Depth Information
To Use the VLOOKUP Function to Determine Letter Grades
To Use the VLOOKUP Function to Determine Letter Grades
The following steps show how to use the VLOOKUP function and the grade table to determine the letter grade
for each sales rep based on the sales rep’s % of Quota value. In this case, cell I9 is the lookup_value; $L$3:$M$7 is the
table_array; and 2 is the col_index_num in the table_array.
With cell J9 selected,
type =vlookup(i9,
$l$3:$m$7, 2 as
the cell entry
(Figure 5–22).
Why are absolute
cell references used
in the function?
It is most important
that you use abso-
lute cell references
($) for the table_
array ($L$3:$M$7) in
the VLOOKUP func-
tion or Excel will not
adjust the cell refer-
ences when it cre-
ates the calculated
column in the next
step. This will cause
unexpected results in
column J.
table is
number of
letter grades
ScreenTip shows
general form
Figure 5–22
Click the Enter button to create a
calculated column for the Grade
fi eld (Figure 5–23).
What happens when the Enter
button is clicked?
Because the cell I9 is the fi rst
record in a table, Excel creates
a calculated column in column I
by copying the VLOOKUP func-
tion through row 21. As shown
in Figure 5–23, any % of Quota
value below 60 in column I returns
a grade of F in column J. The 13th
record (Lopez in row 21) receives a
grade of B because its % of Quota
value is 85.08%. A % of Quota
value of 92% is required to move
up to the next letter grade. The
second record (Dillig) receives a
grade of F because his % of Quota
value is 49.16%, which is less
than 60%.
function assigned
to calculated
VLOOKUP function
returned grades and all
grades automatically
determined in
calculated column
Figure 5–23
Search JabSto ::

Custom Search