Microsoft Office Tutorials and References
In Depth Information
Chapter 20: Lookup Tables
example shows students and their exam scores. You want to look up their scores in
the table and assign them the appropriate grades.
In Figure 20.2, you see the lookup table only. This is the table you are going to
get the information from. The lookup table in Figure 20.2 is in the range of H2:J6. It
is always a better idea to give the range a name. You avoid, as pointed out before,
having to use absolute addressing and it also provides a better feel
The first step is to name the range H2:J6. You will call it
as you can see
in Figure 20.3. The reason you assign a name to the range is that it is more convenient
and less confusing to use a name than absolute addressing when you refer to the data.
Select the data in H2:J6 and type the name Table in the Name box and hit Enter. The
range is now called Table. See Figure 20.3.
Now you can use the VLOOKUP function, which is the very last function under
the Lookup & Reference functions list. Refer to Figure 20.4. The function is called
VLOOKUP because the table in the range H2:J6 is vertical. You would be using
HLOOKUP if the table was horizontal. Select the cell D2 where you want the answer
and use the function f x or try the Shift
F3 shortcut for the Function menu.
You are planning to lookup the score in cell C2 in the table and program how it
converts to a letter grade.
This is how to fill in the Function Arguments window fields:
In this example, the Lookup value is the grade in cell C2 (84).
Table array : This is our lookup table. You called it Table. Use F3 to get the
FIGURE 20.2 Grade Ranges
FIGURE 20.3 Naming the Range