Microsoft Office Tutorials and References
In Depth Information
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.
1
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
ences when it cre-
ates the calculated
column in the next
step. This will cause
unexpected results in
column J.
range
where
table is
located
Enter
button
VLOOKUP
function
column
number of
ScreenTip shows
general form
of VLOOKUP
function
Figure 5–22
2
Click the Enter button to create a
ﬁ eld (Figure 5–23).
What happens when the Enter
button is clicked?
Because the cell I9 is the ﬁ 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
grade of F because his % of Quota
value is 49.16%, which is less
than 60%.
VLOOKUP
function assigned
to calculated
column
VLOOKUP function