Overview
- 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. Excel allows you to perform both vertical lookups (using VLOOKUP functions) and horizontal lookups (using HLOOKUP functions). 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 great majority of formulas using lookup functions involve vertical lookups, we'll concentrate 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])
- Table range is the range that contains the entire lookup table. The table range includes the first column, in which we try and match the lookup value, and any other columns in which we will 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. If range lookup is True or is omitted, the first column of the table range must be in ascending numerical order. If range lookup is True or omitted and an exact match to the lookup value is found in the first column of the table range, Excel keys the lookup off the row of the table in which the exact match is found. If range lookup is True or omitted and an exact match does not exist, Excel keys the lookup off the largest value in the first column that is less than the lookup value. If range lookup is False and an exact match to the lookup value is found in the first column of the table range, Excel keys the lookup off the row of the table in which the exact match is found. If no exact match is obtained, Excel returns an #N/A response (Not Available).
HLOOKUP Syntax
For an HLOOKUP function, simply change 'column' to 'row.' Thus, in an HLOOKUP function, Excel tries to locate the lookup value in the first row (not the first column) of the table range.
Let's explore some interesting examples of lookup functions.
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 follows:
Income Level | Tax Rate |
|---|---|
$0-$9,999 | 15 percent |
$10,000-$29,999 | 30 percent |
$30,000-$99,999 | 34 percent |
$100,000 and over | 40 percent |
To see how to write a formula that computes the tax rate for any income level, open the file Lookup.xls, 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. Then 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), we compute 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.
- 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 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, we 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. The other entries in F13:F17 all 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.xls (see Figure 3-2), you can see the prices for 5 products, listed by their ID code. How do you write a formula that will take a product ID code and return the product price?
Figure 3-2: Looking up prices from product ID codes. In cases like these, 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 we enter in cell I18 the formula VLOOKUP(H18,Lookup2,2, False), the correct price ($5.20) is returned.
You would also use False in a formula designed to look up an employee's salary using the employee's last name or ID number.
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 follows.
Date Sold | Price |
|---|---|
January-April 2005 | $98 |
May-August 2005 | $105 |
September-December 2005 | $112 |
We'll write a formula that will 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 when the price changes in the first row of the table range. See the file DateLookup.xls, 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 will stop at 1/1/05 and return 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 later than 8/01/05, the lookup stops at 8/01/05 and returns the price in D3.
No comments:
Post a Comment