Chapter 10
In this chapter, you learn to use lookup functions to search tables and pull out important bits of information.
Lookup functions let you build spreadsheets that automatically fill in some of the data you need. In this exercise, you'll add lookup functions to a purchase order spreadsheet for a catering company.
Begin by opening the Look Up Catering.xlsx spreadsheet.
This spreadsheet has two worksheets: an Order worksheet where you'll prepare a new order for a customer, and a Price List worksheet that has a list of catering items and the cost of each one. Take a moment to look at both worksheets. The Price List worksheet has all the pricing information, and doesn't need any changes. You'll do your work in the Order worksheet, by writing lookup formulas that get data from the Price List worksheet.
Now take a closer look at the Order worksheet. Here's how it's supposed to work. To add an item to the order, you fill in the Product ID in column A. For example, to purchase coffee (item 1361), you type 1361 into cell A6. At this point, a lookup formula in cell C6 will grab the name of the product from the Price List worksheet. A lookup formula in cell D6 will get the corresponding price. Your job is to write these two lookup formulas.
Start with cell C6. Here, you need a lookup that gets the product name. What function must you use?
VLOOKUP is the ticket, because you're performing a vertical lookup. (Your lookup needs to scan down the Product ID column until it finds the right product.)
The VLOOKUP function needs four parameters. The first parameter is the ID you want to look up. What cell do you use?
A6, which is the product ID for the current row.
The second parameter for VLOOKUP is the range of cells that holds the lookup table. However, there's a twist: the lookup table is on another worksheet, so your cell references need to include the worksheet name. If you don't remember how to write a reference to cells on another worksheet, get Excel to do it for you. Start writing your formula with VLOOKUP. When you get to the second parameter, switch to the Price List sheet and select the table. Then, click back to the Order worksheet.
The cell range should look like this in your formula:
'Price List'!A2:C42
The worksheet name comes first, wrapped in single quotation marks because it has a space. After that is an exclamation mark and the cell reference A2:C42. This gets columns A, B, and C, rows 2 to 42, which includes the full price list and all its data. (Technically, you don't need to include column C, but it's usually easiest to include the whole table and use exactly the same range in all your lookup formulas.)
The third parameter for VLOOKUP is the column index. This is the column with the data you want to retrieve. What number do you need to use to get the product name?
The product name is in the second column in your range, so you use the number 2.
Now you can finish your lookup formula by adding the last parameter, FALSE, which tells Excel that product IDs must match exactly. If you've written your formula correctly, the text "Coffee (per person)" will appear in the cell.
The complete lookup formula looks like this:
=VLOOKUP(A6, 'Price List'!A2:C42, 2, FALSE)
Move to cell D6 and fill in the second lookup formula. This formula is almost the same as the one in C6. The difference is that you need to retrieve the product price instead of the product name.
All you need to change is the column index, from 2 to 3:
=VLOOKUP(A6, 'Price List'!A2:C42, 3, FALSE)
Once you get the product price, something magical happens. The formula in cell E6 multiplies the price of the item (D6) by the number of items you're purchasing (B6). That total shows up in the formula in cell H5, along with the tax in cell H6 and a grand total in H7. These cells use ordinary multiplication, addition, and the SUM function, all of which you've seen before. Take a moment to look at the formulas in these cells before you continue.
Right now, you only have lookup formulas for the first row of your order. Obviously, you need to do some copy and pasting. But before you copy your formulas to the cells underneath, you need to perfect them. The first change is to make your formula deal gracefully with blank values. To see the problem, move to cell A6 and delete the product ID. What happens?
The #N/A error code appears in the cells with the lookup formulas. This causes the total in cell H5 to fail, along with the calculations in cell H6 and H7.
Clearly, you need to find a way to prevent blank lines from causing the #N/A error to appear and sabotaging the total order sum. What function can you use to prevent #N/A errors?
The IFNA function.
Start with the lookup in cell C6. You need to place your lookup inside the IFNA function. If an #N/A error occurs, use empty quotes "" to substitute a blank value. Revise the formula now.
When you're finished, it should look like this:
=IFNA(VLOOKUP(A6, 'Price List'!A2:C42, 2, FALSE), "")
Now make a similar change to the formula in cell D6. This time the IFNA function should substitute a 0 if the lookup fails, because that makes sure the SUM function can add up all the cells in this column.
The revised formula looks like this:
=IFNA(VLOOKUP(A6, 'Price List'!A2:C42, 3, FALSE), 0)
Because the cell is using the Accounting number format, when a 0 appers in the cell it's actually shown as a dash.
To make this example work, you need to copy this lookup formula to every order line. However, you're not ready to take that step yet. Right now your formula has two relative references, and one of them is going to cause a problem when you start making formula copies. Which reference needs to be changed, and what should it look like in the revised formula?
The price list should be changed to a fixed reference. That's because no matter where you copy your formula, you want it to keep using the same price list. Here's what the reference should look like, with the $ signs that prevent Excel from changing the row numbers or column letters:
'Price List'!$A$2:$C$42
The lookup value should stay as it is: a relative reference. That allows Excel to adjust it. For example, when you copy your formula to C7 it will use use the lookup value in A7.
Edit both lookup formulas to change the price list reference into a fixed reference.
Copy the formulas in cell C6 and cell D6 to all the order lines underneath.
Now that the order form is finished, try it out. Find a product in the Price List worksheet, and type the ID into the Product ID column to add it to your order. Fill in the Quantity column, and watch Excel calculate the total for that line and add it to the total cost for the entire order. See what happens if you type in a product ID that doesn't exist in the price list. If you're solution doesn't seem quite right, you can review the solution version of this spreadsheet, Look Up Catering [Solution].xlsx.