Using lookup functions
Lookup tables are useful when you want to compare a particular value to a set of values, and depending on where your value falls, assign a given “answer.” For example, you might have a tax table that shows, for any gross adjusted income, what the corresponding tax is. There are two versions of lookup tables, vertical (VLOOKUP) and horizontal (HLOOKUP). Since they are virtually identical except that vertical goes down whereas horizontal goes across, we’ll only discuss the VLOOKUP function.
The VLOOKUP function takes three arguments: (1) the value to be compared, (2) a table of lookup values, with the values to be compared against always in the leftmost column, and (3) the column number of the lookup table where you find the “answer.” Since the VLOOKUP function is often copied down a column, it is usually necessary to make the second argument an absolute reference, and this is accomplished most easily by giving the lookup table a range name such as LookupTable. (Range names are always treated as absolute references.)
The only requirement of a lookup table is that the values in the first column (the comparison column) must be sorted in ascending order. Let’s say you want to assign letter grades to students based on a straight scale: below 60, an F: at least 60 but below 70, a D; at least 70 but below 80, a C; at least 80 but below 90, a B; and 90 or above, an A. The spreadsheet sample below shows how you would set this up. The comparison column in the lookup table starts at 0 (the lowest grade possible), then records the cutoff scores 60 through 90. The lookup table in the range E2:F6 is range-named LookupTable.
The typical formula in cell C2 (which is copied down column C) is =VLOOKUP(B2,LookupTable,2). This compares the value in B2 (67) to the values in column E and chooses the largest value less than or equal to it. This is 60. Then since the last argument in the VLOOKUP function is 2, the score reported in C2 comes from the second column of the lookup table next to 60, namely, D.
To use a VLOOKUP function:
Create a lookup table with at least
two columns, where the values in the first column are in ascending order, and
(for best results) give the table range a range name. Then enter the formula =VLOOKUP(value,lookup table,column #), as described
above.
Try it! Enter a lookup table in columns E and F, and
VLOOKUP functions in column C. Assume
there is a quantity discount policy in force: for orders less than 300 units,
the unit price is $3; for orders of at least 300 units but less than 400, the
unit price is $2.50; for orders of 400 units or more, the unit price is $2.
(Scroll to the right to see the correct answer.)
Data tables, also called what-if tables, allow you to see very quickly how one or more outputs change as one or two key inputs change. There are two types of data tables: one-way tables and two-way tables. A one-way table has one input and any number of outputs. A two-way table has two inputs but only one output. We’ll demonstrate both types.
To illustrate, suppose Mr. Jones buys a new car for $20,000, makes a $5,000 down payment, and finances the remaining amount over the next 36 months at an 8.5% annual interest rate. There are at least two outputs that might be of interest: the monthly payment and the total interest paid. These are affected by at least two inputs: the amount of the down payment and the annual interest rate.
Let’s first look at a simple one-way data table, where we see how a single output, monthly payment, varies as the annual interest rate varies. This is shown in the following spreadsheet.
To create the above table (which we already did):
Enter the formula for the output in
cell E3. (Since the monthly payment was
calculated with the PMT function in cell B7, simply enter =B7 in cell E3.) Starting in cell D4, enter any sequence of
interest rates. Select the entire table,
that is, the range D3:E8. Finally, use
the Data/Table menu item and enter B4 as the column input cell. (There is no row input cell, so leave it
blank.)
What happens when you do this? Excel takes each interest rate in column D,
substitutes it into the column input cell you designated (cell B4),
recalculates the formula in cell E3 (the one we colored gray for emphasis) with
this new interest rate, and records the answer in the data table. We use a column input cell because the
possible inputs (interest rates) are listed in a column.
We can also capture more than one output in a one-way data table. An example appears below, where the single input is still the interest rate, but there are two outputs: monthly payment and total interest paid. This table is formed exactly as before except that the table range is now D3:F8.
Try it! Create a one-way data table that shows the
monthly payments and the total interest paid for each term (value in cell B5)
from 12 to 48 in increments of 12. (Scroll to the right for the correct
answer.)
Two-way tables allow you to vary two inputs, one along a row and one along a column, and capture a single output in the body of the table. The following spreadsheet illustrates this, where we vary the annual interest rate and the amount of the down payment. The single output is the monthly payment.
To create the above table (which we already did):
Enter the formula (=B7) for the
single output in the upper left corner (cell D2) of the data table. (Again, we colored this cell gray for
emphasis.) Enter any sequence of down
payments to the right of this and any sequence of interest rates below
this. Finally, select the entire data
table range, D2:G7, use the Data/Table menu item, enter B2 as the row input
cell, and enter B4 as the column input cell.
Note that B2 is the row
input cell because various down payments are entered in a row (E2:G2). Similarly, B4 is the column input cell
because interest rates are entered in a column.
Excel substitutes each down payment into cell B2, each interest rate
into cell B4, calculates the formula in cell D2, and records the answer
(monthly payment) in the body of the table.
Try it! Create a two-way data table that shows the
total interest paid for each term from 12 to 48 in increments of 12, and each
down payment from $3000 to $5000 in increments of $1000. Put down payments along the side, terms along
the top. (Scroll to the right for the correct answer.)
