Lookup functions

Answers to this chapter’s questions

How do 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 the following table.

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

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

I began by entering the relevant information (tax rates and break points) in cell range D6:E9. I named the table range D6:E9 lookup. I recommend that you always 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 illustrate how the lookup function works, I entered some incomes in the range D13:D17. By copying from E13:E17 the formula VLOOKUP(D13,Lookup,2,True), I computed the tax rate for the income levels listed in D13:D17.

Let’s 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, simply replace the 0 in D6 by 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.

• 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, I changed the value of the range lookup argument from True to False and copied from F13 to F14:F17 the formula VLOOKUP(D13,Lookup,2,False). 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 have 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, you need to remember only one simple rule: use False as the value of the range lookup argument.

Here’s an example. In the file Lookup.xlsx (see Figure 3-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 3-2 Looking 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 I have 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. Because the product IDs in the table range Lookup2 (H11:I15) are not listed in alphabetical order, an incorrect price ($3.50) is returned. If you enter the formula VLOOKUP(H18,Lookup2,2,False) in cell I18, the correct price ($5.20) is returned, as shown in cell I19.

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

By the way, you can see in Figure 3-2 that columns A–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, click Format, point to Hide & Unhide (under Visibility), and then click 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.

Let’s write a formula to determine the correct product price for any date on which the product is sold in the year 2005. For variety, we’ll use an HLOOKUP function. I’ve placed the dates for when the price changes in the first row of the table range. See the file Datelookup.xlsx, shown in Figure 3-3.

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

I copied from C8 to C9:C11 the formula HLOOKUP(B8,lookup,2,TRUE). This formula tries to match the dates in column B with the first row of the range B2:D3. 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/01/05 and 7/31/05, the lookup stops at 5/1/05 and returns the price in C3; and for any date on or later than 8/01/05, the lookup stops at 8/01/05 and returns the price in D3.