Microsoft Office Tutorials and References

In Depth Information

**Chapter 20: Lookup Tables**

CHAPTER
20

Lookup Tables

Lookup tables are a good way to search for information and to return specific data

relating to the information you introduce or look for. The Lookup function is a

quick way to find the information one needs, and is particularly efficient when

dealing with large databases that would be extremely time-consuming to search

manually. This chapter will introduce a vertical lookup table in two ways or con-

figurations. The first one is when you are looking to return a single value within a

certain data range. The second way will demonstrate a request for an exact match.

When you create a vertical lookup table, the left column of the table is where you

define the information you want to look up. The first column contains the unique

values on which you base the lookup search.

RANGE—APPROXIMATE MATCH LOOKUP

This lookup is a way to find a value that does not have an exact match, but would fall

within a certain range. Consider an example of assigning grades to students based on

their scores on an exam. The table in H2:J6, in Figure 20.1, indicates the range of

values for exam grades. The left column has the scores and the other two columns

specify the grades in two formats. This lookup is called, by Excel, a range lookup

—

since you will get the grade if you are within a certain range. For example, if the

student has a score in the range of 76 to 85, then the letter grade received should be a

“

Note that the B or the letter grade is in the second column and the grade in

a word version is in the third column of the table. This table does not need an exact

match. For another example, any score that equals to 66, but is less than 76, will

result in a grade of C or Satisfactory.
This is why it is not an exact match.
The

B.

”

FIGURE 20.1
Grade Sheet Lookup Table