Chapter 7 Working with Tables176
Try It
Use the Major field filtering to show the scores for two different majors, Busi-
ness and Liberal Arts. Change the Values field setting to show the minimum
scores for filtered majors.
Lookup Tables
We now consider another type of tables, lookup tables that serve a totally different
purpose. A lookup table is a special type of table whose entries are fixed and cannot
be modified. An example of a lookup table is the income tax rate table which specifies
how much tax is due given the adjusted gross income and marital status of the tax filer.
Another example of a lookup table is the telephone directory where we can look up a
persons phone number based on the last name.
Excel provides several different functions for use in connection with lookup tables.
Three of these functions, VLOOKUP, HLOOKUP, and LOOKUP, will be discussed here. A
lookup function performs a task similar to looking for the telephone number of a person
given her name. That is, you have a part of the information stored in the lookup table
(the name of a person) and you want to extract part of the remaining information (the
phone number in this case).
The VLOOKUP function works with lookup tables that are column or vertically
oriented. It looks for the specified or search value in a column of the lookup table
and returns the corresponding value from another column of the table. It has the
form VLOOKUP(lookup_value, lookup_table_range, return_col_numb,
range_lookup). The first argument specifies the value we want to look up in the first
Figure 7.37
The student performance pivot table with the Major fi eld fi ltered on education.
Lookup Tables 177
column of the lookup table. The second argument specifies a range of cells where the
table is stored in the worksheet. The third argument specifies the column number from
which the value returned by the function is taken. The fourth argument is an optional
argument. It can be either TRUE or FALSE. If it is omitted, its value is treated as TRUE.
When this argument is TRUE, the VLOOKUP function will return the result based on an
approximate match if an exact match is not present. The approximate match is taken as
the highest value that is less than the lookup value. Excel requires that the values in the
first column of the lookup table be in ascending order if the range_lookup argument
is given as TRUE or is omitted. When the optional argument is set to FALSE, the func-
tion searches for an exact match only and returns #N/A if no match is found.
As an example of VLOOKUP function, consider the information shown in Table 7.1.
The table has information organized in three columns. For each person, we have the
name in the first column, phone number in the second column, and address in the
third column. The formula =VLOOKUP("Jack",A2:C6,2) will return 734-111-
, the phone number of Jack, because Jackis the value the function looks for
in the first column of the table and it looks for a corresponding value in column
two. If we change the formula to =VLOOKUP("Jack",A2:C6,3), then the value
returned by the formula will be 9012 Noway, Jacks address from the third column.
Suppose we made an error while entering the lookup value in our formula and entered
Jeckin the formula =VLOOKUP("Jeck",A2:C6,3). We will still get the result
9012 Noway because of the approximate match. However, if we enter the formula
=VLOOKUP("Jeck",A2:C6,3,FALSE), we are forcing an exact match and the value
returned by the formula will be #N/A.
The HLOOKUP function works in a similar manner except that the lookup table is orga-
nized along rows. As an example of the HLOOKUP function, let us consider the lookup
table shown in Table 7.2. The table consists of two rows. The first row, marked Score,
carries cutoff scores and the second row carries the corresponding letter grades. The
value returned by the formula =HLOOKUP(256,B1:F2,2) will be C. This is because
the lookup value of 256 is not present in the table and an approximate match is allowed.
The highest value that is less than 256 is 200 in the table. Thus, the function will return
the value from the corresponding position in the second row.
Table 7.1 Address Book Data
Name Phone # Address
Bill 248-555-1212 1234 Freeway
Chad 313-999-3131 5678 Highway
Jack 734-111-2222 9012 Noway
Pam 810-100-1234 3456 Everyway
Rani 248-888-0123 7890 Beltway

Get Computing with Excel and VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.