Excel users often need to retrieve data and repurpose it in many different ways. For example, you might use sales figures in an annual report or create invoices from an inventory list. But pulling data out of large, complicated Excel spreadsheets by hand is a pain. So stop searching—by creating a
you can have Excel automatically pull the data you need. These handy formulas save time and prevent data-entry errors.
I’ll show you how to use Excel 2004’s Lookup Wizard, as well as how—and when—to write your own lookup formula. (If you use Excel X or 2001, you must install the Office Value Pack to access the wizard. For installation instructions, click
Quick Lookups with the Wizard
Excel’s Lookup Wizard provides an easy step-by-step process for retrieving data from tables. It’s best used with a two-dimensional table. Use it to find a cell at the intersection of a particular column and row.
For example, say you have a gigantic spreadsheet that details the cost of shipping packages of different weights to each state in the United States. The table lists weights along the top and states along the side. You could scroll through your spreadsheet to find out how much sending a 2-pound package to California would cost—but the Lookup Wizard provides a faster option.
To find the number you need, select the entire table, including the row and column headings, and choose Tools: Wizard: Lookup. The range of table cells automatically appears in the dialog box, so you can just click on Next. Choose 2.0 from the first drop-down menu and CA from the second, and then click on Next. At this point, the dialog box shows the shipping price.
If you stop here (or choose the option Copy Just The Formula To A Single Cell), then the next time you need to find out the cost of shipping a package, you must step through the entire Lookup Wizard process again. Save yourself the trouble by choosing the option Copy The Formula And Lookup Parameters. This way, you can reuse the work that the wizard did for you, and simply tweak the parameters to reflect your new search. For example, you could type TN instead of CA to see how much it would cost to ship a package to Music City. You’d click on Next and, when prompted, select some empty cells in your worksheet. Then you’d click on OK to paste in the formula and the parameters.
When to Go DIY
The Lookup Wizard is less useful when you need to look up many pieces of information in a worksheet at the same time. Say you have an Excel table of every product you sell through your catalog, along with product descriptions and prices. When creat-ing an invoice, you’d like to just enter the product numbers and have Excel automatically insert the appropriate descriptions and prices from the table.
The Lookup Wizard doesn’t work so well here because you’d have to step through the process once for each description
once for each price. A better option is to write your own formula using the Excel
function. Using this function has an added benefit: if you accidentally type the wrong product number and later type the correct one in its place, the formula will automatically update and return the correct data for that product number.
Write the Formula
Let’s assume that your table includes the product numbers in the first column, descriptions in the second, and prices in the third. To create your formula, you first need to assign a name to the range containing this data. Select the table, including the row headings but
the column headings. Then choose Insert: Name: Define and type a name for the table—for example, ProductDetails. Click on OK. This will allow you to reference the table easily in your formula.
Now, in the invoice area of your worksheet, type the product number for the first invoice item. (For this example, assume that the product number is in cell B19.) Then type the
formula in the cell where the description should appear. The formula to extract the description for this product number would look like this:
function takes three compulsory arguments and one optional one. The first argument is the value to look up. Here you’re looking up a value (in this case, the product number) stored in cell B19. The second argument is the table containing the data, which you named ProductDetails. The third argument is the column number in the table that contains the data you want—the description, located in column 2.
The final argument tells the formula whether to look for an exact match or the closest match. You could leave this argument out, but the function will default to True. In that case, if the formula couldn’t find an exact match, it would take the next largest value. (The data in the first column must be sorted for this to work.) You’re looking up product numbers and need an exact match, so you set this argument to False. The formula will therefore find an exact match or return an error (
). Conveniently, when you choose False, the values in the first column don’t have to be sorted.
Copy this formula down your invoice’s description column to return the product description for all the product numbers. To return prices, simply change the column value from 2 to 3 (to return the data from the third column), and then copy the new formula down the invoice’s price column.
Trade Data between Worksheets
To keep things simple, this example worked with a product list and an invoice located in the same worksheet, but that’s often not the case. The
formula can easily return data from another sheet in the same workbook or in another workbook.
If you make sure that you give ranges in your workbook unique names (so, for instance, there’s only one ProductDetails), the existing formula will work for data stored in a different worksheet. To retrieve data from another workbook, you’ll need to add the file name and an exclamation mark before the named range. Here’s an example:
Address Different Table Types
What happens if your table is arranged in the
layout—in other words, if the value you want to match up is in a column, not a row? You need to pull a result by referencing a row number. Another function,
, works like
, but it finds data in a
Extending Your Excel Expertise
Lookup formulas can save you time and trouble. Because they extract data from a table, they update if the data changes. And they return exactly the details requested every time, avoiding keying and transcribing errors and speeding up your work.
Helen Bradley has written for numerous small-business and computer publications and is a frequent contributor to
Find a single piece of data fast with Excel’s Lookup Wizard.