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

person’s 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 ﬁ eld ﬁ 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-

2222

, the phone number of Jack, because “Jack” is 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, Jack’s address from the third column.

Suppose we made an error while entering the lookup value in our formula and entered

“Jeck” in 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.