CHAPTER 2

Lookup functions

Questions answered in this chapter:

■■ How can I write a formula to compute tax rates based on income?

■■ Given a product ID, how can I look up the product’s price?

■■ Suppose that a product’s price changes over time. I know the date the product was sold. How
can I write a formula to compute the product’s price?

Syntax of the lookup functions

Lookup functions enable you to look up values from worksheet ranges. In Microsoft Excel 2013, you
can perform both vertical lookups (by using the VLOOKUP function) and horizontal lookups (by
using the HLOOKUP function). In a vertical lookup, the lookup operation starts in the first column of
a worksheet range. In a horizontal lookup, the operation starts in the first row of a worksheet range.
Because the majority of formulas using lookup functions involve vertical lookups, this chapter con-
centrates on VLOOKUP functions.

VLOOKUP syntax
The syntax of the VLOOKUP function is as follows. The brackets ([ ]) indicate optional arguments.

VLOOKUP(lookup value,table range,column index,[range lookup])

■■ Lookup value is the value you want to look up in the first column of the table range.

■■ Table range is the range that contains the entire lookup table. The table range includes the
first column, in which you try to match the lookup value, and any other columns in which you
want to look up formula results.

■■ Column index is the column number in the table range from which the value of the lookup
function is obtained.

■■ Range lookup is an optional argument. The point of range lookup is to specify an exact or
approximate match. If the range lookup argument is True or omitted, the first column of the
table range must be in ascending numerical order. If the range lookup argument is True or
omitted and an exact match to the lookup value is found in the first column of the table

15

 range, Excel bases the lookup on the row of the table in which the exact match is found. If the
range lookup argument is True or omitted and an exact match does not exist, Excel bases the
lookup on the largest value in the first column that is less than the lookup value. If the range
lookup argument is False and an exact match to the lookup value is found in the first column
of the table range, Excel bases the lookup on the row of the table in which the exact match
is found. If no exact match is obtained, Excel returns an #N/A (Not Available) response. Note
that a range lookup argument of 1 is equivalent to True, whereas a range lookup argument of
0 is equivalent to False.

HLOOKUP syntax
In an HLOOKUP function, Excel tries to locate the lookup value in the first row (not the first column)
of the table range. For an HLOOKUP function, use the VLOOKUP syntax and change column to row.

Now, explore some interesting examples of lookup functions.

Answers to this chapter’s questions

This section provides the answers to the questions that are listed at the beginning of the chapter.

How can I write a formula to compute tax rates based on income?

The following example shows how a VLOOKUP function works when the first column of the table
range consists of numbers in ascending order. Suppose that the tax rate depends on income, as
shown in Table 2-1.

TABLE 2-1  Tax rate on income

Income level Tax rate

$0–$9,999 15%
$10,000–$29,999 30%
$30,000–$99,999 34%
$100,000 and over 40%

To see an example of how to write a formula that computes the tax rate for any income level, open
the Lookup.xlsx file, shown in Figure 2-1.

16  Chapter 2  Lookup functions

 FIGURE 2-1  Use a lookup function to compute a tax rate. The numbers in the first column of the table range are
sorted in ascending order.

First, the relevant information (tax rates and break points) was entered in cell range D6:E9. The
table range is named D6:E9 lookup. It’s recommended always to name the cells you’re using as the
table range. If you do so, you need not remember the exact location of the table range, and when
you copy any formula involving a lookup function, the lookup range will always be correct. To illus-
trate how the lookup function works, some incomes were entered in the D13:D17 range. By copying
the VLOOKUP(D13,Lookup,2,True) formula from E13 to E14:E17, the tax rate was computed for the
income levels listed in D13:D17. Examine how the lookup function worked in cells E13:E17. Note that
because the column index in the formula is 2, the answer always comes from the second column of
the table range.

■■ In D13, the income of –$1,000 yields #N/A because –$1,000 is less than the lowest income
level in the first column of the table range. If you want a tax rate of 15 percent associated with
an income of –$1,000, replace the 0 in D6 with a number that is –1,000 or smaller.

■■ In D14, the income of $30,000 exactly matches a value in the first column of the table range,
so the function returns a tax rate of 34 percent.

■■ In D15, the income level of $29,000 does not exactly match a value in the first column of the
table range, which means the lookup function stops at the largest number less than $29,000
in the first column of the range—$10,000 in this case. This function returns the tax rate in
column 2 of the table range opposite $10,000, or 30 percent.

Lookup functions  Chapter 2   17

 ■■ In D16, the income level of $98,000 does not yield an exact match in the first column of the
table range. The lookup function stops at the largest number less than $98,000 in the first
column of the table range. This returns the tax rate in column 2 of the table range opposite
$30,000—34 percent.

■■ In D17, the income level of $104,000 does not yield an exact match in the first column of the
table range. The lookup function stops at the largest number less than $104,000 in the first
column of the table range, which returns the tax rate in column 2 of the table range opposite
$100,000—40 percent.

In F13:F17, the value of the range lookup argument was changed from True to False, and the
VLOOKUP(D13,Lookup,2,False) formula was copied from F13 to F14:F17. Cell F14 still yields a 34
percent tax rate because the first column of the table range contains an exact match to $30,000. All
the other entries in F13:F17 display #N/A because none of the other incomes in D13:D17 has an exact
match in the first column of the table range.

Given a product ID, how can I look up the product’s price?

Often, the first column of a table range does not consist of numbers in ascending order. For example,
the first column of the table range might list product ID codes or employee names. In my experience
teaching thousands of financial analysts, I’ve found that many people don’t know how to deal with
lookup functions when the first column of the table range does not consist of numbers in ascending
order. In these situations, remember only one simple rule: Use False as the value of the range lookup
argument.

Here’s an example. In the Lookup.xlsx file (see Figure 2-2), you can see the prices for five products,
listed by their product ID code. How do you write a formula that takes a product ID code and returns
the product price?

FIGURE 2-2  Look up prices from product ID codes. When the table range isn’t sorted in ascending order, enter
False as the last argument in the lookup function formula.

Many people would enter the formula as in cell I18: VLOOKUP(H18,Lookup2,2). However, note that
when you omit the fourth argument (the range lookup argument), the value is assumed to be True.

18  Chapter 2  Lookup functions

 Because the product IDs in the Lookup2 (H11:I15) table range are not listed in alphabetical order, an
incorrect price ($3.50) is returned. If you enter the VLOOKUP(H18,Lookup2,2,False) formula in cell I18,
the correct price ($5.20) is returned.

You would also use False in a formula designed to find an employee’s salary by using the employ-
ee’s last name or ID number.

By the way, you can see in Figure 2-2 that columns A to G are hidden. To hide columns, you begin
by selecting the columns you want to hide. Click the Home tab on the ribbon. In the Cells group,
choose Format, point to Hide & Unhide (under Visibility), and then choose Hide Columns.

Suppose that a product’s price changes over time. I know the date the product was sold. How
can I write a formula to compute the product’s price?

Suppose the price of a product depends on the date the product was sold. How can you use a lookup
function in a formula that will pick up the correct product price? More specifically, suppose the price
of a product is as shown in the following table:

Date sold Price

January–April 2005 $98
May–July 2005 $105
August–December 2005 $112

Write a formula to determine the correct product price for any date on which the product is sold
in the year 2005. For variety, use an HLOOKUP function. The dates when the price changes are in the
first row of the table range. See the Datelookup.xlsx file, shown in Figure 2-3.

FIGURE 2-3  Use an HLOOKUP function to determine a price that changes depending on the date it’s sold.

The HLOOKUP(B8,lookup,2,TRUE) formula is copied from C8 to C9:C11. This formula tries to match
the dates in column B with the first row of the B2:D3 range. At any date between 1/1/05 and 4/30/05,
the lookup function stops at 1/1/05 and returns the price in B3; for any date between 5/1/05 and

Lookup functions  Chapter 2   19

 7/31/05, the lookup stops at 5/1/05 and returns the price in C3; and for any date later than 8/1/05,
the lookup stops at 8/1/05 and returns the price in D3.

Problems
1. The Hr.xlsx file gives employee ID codes, salaries, and years of experience. Write a formula
that yields the employee’s salary from a given ID code. Write another formula that yields the
employee’s years of experience from a given ID code.

2. The Assign.xlsx file gives the assignment of workers to four groups. The suitability of each
worker for each group (on a scale from 0 to 10) is also given. Write a formula that gives the
suitability of each worker for the group to which the worker is assigned.

3. You are thinking of advertising Microsoft products on a sports telecast. As you buy more ads,
the price of each ad decreases as shown in the following table:

Number of ads Price per ad

1–5 $12,000
6–10 $11,000
11–20 $10,000
21 and higher $9,000

For example, if you buy 8 ads, you pay $11,000 per ad, but if you buy 14 ads, you pay $10,000
per ad. Write a formula that yields the total cost of purchasing any number of ads.

4. You are thinking of advertising Microsoft products on a popular TV music program. You pay
one price for the first group of ads, but as you buy more ads, the price per ad decreases as
shown in the following table:

Ad number Price per ad

1–5 $12,000
6–10 $11,000
11–20 $10,000
21 or higher $9,000

For example, if you buy 8 ads, you pay $12,000 per ad for the first 5 ads and $11,000 for each
of the next 3 ads. If you buy 14 ads, you pay $12,000 for each of the first 5 ads, $11,000 for
each of the next 5 ads, and $10,000 for each of the last 4 ads. Write a formula that yields the
total cost of purchasing any number of ads. Hint: You probably need at least three columns in
your table range, and your formula might involve two lookup functions.

20  Chapter 2  Lookup functions

 5. The annual rate your bank charges you to borrow money for 1, 5, 10, or 30 years is shown in
the following table:

Duration of loan Annual loan rate

1 year 6%
5 years 7%
10 years 9%
30 years 10%

If you borrow money from the bank for any duration from 1 through 30 years that’s not listed
in the table, your rate is found by interpolating between the rates given in the table. For
example, you borrow money for 15 years. Because 15 years is one quarter of the way between
10 years and 30 years, the annual loan rate would be calculated as follows:
1 3
(9) + (10) = 9.75%
4 4

Write a formula that returns the annual interest rate on a loan for any period between 1 and
30 years.

6. The distance between any two US cities (excluding cities in Alaska and Hawaii) can be approxi-
mated by this formula:

69 * (lat1 - lat2)2 + (long1 - long2)2

The Citydata.xlsx file contains the latitude and longitude of selected US cities. Create a table
that gives the distance between any two of the listed cities.

7. In the Pinevalley.xlsx file, the first worksheet contains the salaries of several employees at Pine
Valley University, the second worksheet contains the age of the employees, and the third
worksheet contains the years of experience. Create a fourth worksheet that contains the sal-
ary, age, and experience for each employee.

8. The Lookupmultiplecolumns.xlsx file contains information about several sales made at an

electronics store. A salesperson’s name will be entered in B17. Write an Excel formula that can
be copied from C17 to D17:F17 that extracts each salesperson’s radio sales to C17, TV sales to
D17, printer sales to E17, and CD sales to F17.

Lookup functions  Chapter 2   21

 9. The Grades.xlsx file contains students’ grades on an exam. Suppose the curve is as follows:

Score Grade
Below 60 F
60–69 D
70–79 C
80–89 B
90 and above A

Use Excel to return each student’s letter grade on this exam.

10. The Employees.xlsx file contains the ranking each of 35 workers has given (on a 0–10 scale) to
three jobs. The file also gives the job to which each worker is assigned. Use a formula to com-
pute each worker’s ranking for the job to which the worker is assigned.

11. Suppose one dollar can be converted to 1,000 yen, 5 pesos, or 0.7 euros. Set up a spreadsheet
in which the user can enter an amount in US dollars and a currency, and the spreadsheet con-
verts dollars to the entered currency.

22  Chapter 2  Lookup functions