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

adjust the cell refer-

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

letter grades

ScreenTip shows

general form

of VLOOKUP

function

Figure 5–22

2

•
Click the Enter button to create a

calculated column for the Grade

ﬁ 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

second record (Dillig) receives a

grade of F because his % of Quota

value is 49.16%, which is less

than 60%.

VLOOKUP

function assigned

to calculated

column

VLOOKUP function

returned grades and all

grades automatically

determined in

calculated column

Figure 5–23