Microsoft Office Tutorials and References
In Depth Information
Adding a Lookup Table
The grading scale in this chapter (Table 5–3) is similar to one
that your instructor uses to determine your letter grade. As shown in
Table 5–3, any score greater than or equal to 92% equates to a let-
ter grade of A. Scores greater than or equal to 80 and less than 92 are
assigned a letter grade of B, and so on.
The VLOOKUP function requires that the table indicate only
the lowest score for a letter grade. Furthermore, the table entries must
be in sequence from lowest score to highest score. Thus, the entries in
Table 5–3 must be resequenced for use with the VLOOKUP function so
they appear as shown in Table 5–4.
The general form of the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num)
Table 5–3 Typical Grade Table
% of Quota
92% and higher
80% to 91%
70% to 79%
60% to 69%
0 to 59%
Table 5–4 Typical Grade Table Modiﬁ ed
for VLOOKUP Function
% of Quota
To Create a Lookup Table
The VLOOKUP function searches the far-left column of the table
array . The far-left column of the table_array contains what are called
the table arguments . In this example, the table arguments are made up
of percentages (see Table 5–4). The VLOOKUP function uses the %
of Quota value (called the lookup_value) in the record of a sales rep to
search the far-left column of the table array for a particular value and then returns the
corresponding table value from the column indicated by the col_index_num value. In this
example, the grades are in the second or far-right column.
For the VLOOKUP function to work correctly, the table arguments must be in
ascending sequence, because the VLOOKUP function will return a table value based on
the lookup_value being less than or equal to the table arguments. Thus, if the % of Quota
value is 77.61% (ﬁ fth record in table), then the VLOOKUP function returns a grade of C,
because 77.61% is greater than or equal to 70% and less than 80%.
The following steps create the grade table in the range L1:M7.
1 Select column headings L and M. Point to the boundary on the right side of the column
M heading above row 1 and then drag to the right until the ScreenTip indicates, Width:
11.00 (82 pixels).
The VLOOKUP Function
A score that is outside
the range of the table
causes the VLOOKUP
function to return an
error message (#N/A) to
the cell. For example, any
% of Quota score less
than zero in column I of
Figure 5–20 would result
in the error message #N/A
being assigned to the
2 Select cell L1 and then enter Grade Table as the table title.
3 If necessary, scroll the worksheet to the left and click cell A7 to select it. Scroll the
worksheet to the right so that cell L1 is visible. Click the Format Painter button on the
Ribbon and then click cell L1. Drag through cell M1 and then click the Merge & Center
button on the Home tab on the Ribbon.
4 Select the range I8:J8. While holding down the CTRL key, point to the border of the
range I8:J8 and drag to the range L2:M2 to copy the column headings, % of Quota