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 configurations. 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 “B.” Note that the B or the letter grade is in the second column and the grade ...
Get Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
