By Colin Wilcox
This column provide more information about ways to use the HLOOKUP and VLOOKUP functions in Excel, along with instructions for using the Lookup Wizard, an add-in that comes with Excel. The tips include shortcuts and advice on using a mix of absolute and relative cell references to return multiple search results.
|Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002
The last Power User column, Use HLookup and VLookup to find records in large worksheets, explained the basics of using the HLOOKUP and VLOOKUP functions in Microsoft Excel to find records in a large worksheet. This column explains several other ways to use those functions.
To refresh your memory from last time: My friend uses Microsoft Access to store and manage data about the number of hits that her Web site receives. She imports the data into Excel for analysis. She'd heard that lookup functions could save time by finding related data in the various sheets.
To follow the steps in this column, you need the sample worksheet from the last column, called LookupFunctions.xls. If you haven't already, follow the steps in the last column to get ready to do the steps in this one. The sample worksheet uses fictitious Web site data that demonstrates my friend's problem. The spreadsheet contains a worksheet called Page Views (with a set of IDs that uniquely identify each site page and the hits it receives) and another called Pages (with the page IDs and the names of the pages that correspond to each ID).
Let ToolTips help you write functions
When you enter a formula in Excel, a ToolTip appears and lists the arguments available for that function. (If a ToolTip does not appear, select Options on the Tools menu, and on the General tab, under Settings, select the Function tooltips check box.) Those ToolTips contain hyperlinks, and you can use the links to speed up the process of selecting cell ranges and embedding the correct arguments in your formulas. Follow these steps:
- In the sample worksheet, click the Page Views tab, and then clear the VLOOKUP and HLOOKUP formulas and all text in Columns E and F.
- In Cell F4, type:
When you type the opening parenthesis, a ToolTip appears.
- Click lookup_value in the ToolTip, and then click Cell F3. Excel writes the value F3 next to the opening parenthesis.
- Type a comma after F3 in the formula, and then click the Pages tab.
- In the Pages worksheet, highlight the cell range A2 to B39. The string Pages!A2:B39 appears in the formula bar.
- In the formula bar, type a comma after Pages!A2:B39. Entering a comma moves you to the next option in the ToolTip.
- Enter the rest of the formula as shown: 2,false), and then press ENTER. Excel returns you to the Page Views worksheet, and #N/A appears in Cell F4, indicating that you need to enter a search value in Cell F3. Typically, the #N/A error appears when your function tries to reference an empty cell. For more information about fixing #N/A errors, see Correct a #N/A error.
- Copy one of the page ID values from Column A to Cell F3, and then press ENTER. The corresponding page name appears in Cell F4.
You can follow these steps whenever you get stuck on any formula: Type an equal sign, the formula name, and the opening parenthesis, and then follow the ToolTip. Remember to separate your arguments with commas.
Use relative and absolute cell references to return multiple results
The instance of VLOOKUP that you created in the previous section contains a reference to Cell F3. Excel calls that type of reference a relative reference, meaning that if you copy the formula down or across a range of cells, Excel automatically increases the cell reference by 1 for each new instance of the formula. If any of those instances of the formula reference empty cells, Excel displays the #N/A error.
In contrast, absolute cell references do not change when you copy them down or across a range of cells, so using them can help you avoid #N/A errors. The following steps demonstrate how relative cell references can cause #N/A errors, and how absolute references can fix them.
- On the Page Views worksheet, copy and paste or type this formula into Cell D4:
- Click Cell D4, rest your mouse pointer on the lower-right corner of the cell until it changes to a black plus sign (+), and then drag the mouse pointer down to Cell D41. The page names that correspond to each ID appear in Column D.
At this point, #N/A appears in several cells. What happened? If you click cells in Column D, you see that Excel also increased the cell references by one after the word Pages! in the formula. (In case you haven't read the previous article, "Pages!A2:B39" is called the table array argument. For more information about the argument, read the previous article.) In other words, the formula in Cell D4 references Cells A2 through B39 on the Pages worksheet, which is what you want. But look at the formula associated with Cell D5: It references Cells A3 through B40. If you go back to the Pages worksheet, you'll see that Cell B40 is empty. Excel returns the #N/A message because some instances of the formula contain references to empty cells.
To work around the problem, use absolute cell references. Absolute references prevent Excel from changing cell references in a formula when you copy that formula. Follow these steps:
- In the Page Views worksheet, clear Cells D5 through D41.
- In Cell D4 (or the formula bar, if that's easier), add dollar signs to the formula as shown:
The dollar signs make the cell references absolute.
- Point to the lower-right corner of Cell D4 until your mouse pointer changes to a black plus sign, and then drag the pointer down to Cell D41. As you copy the formula, the dollar signs prevent Excel from changing any cell references in the table array argument. This time, the page names that correspond to each ID appear in Column D with no errors.
The formula in Step 2 uses absolute columns and rows (the dollar signs are a dead giveaway). You can use a mix of relative columns and absolute rows, or vice versa. For example:
- If you need a relative column reference and an absolute row reference, use A$2.
- If you need an absolute column reference and a relative row reference, use $A2.
You can mix absolute and relative references as needed. For example, you could use $A2:B$39, or any other combination of characters. Just make sure that you place the dollar sign before the column or row that you want to designate as an absolute reference. The function fails otherwise.
Finally, for more information about formula error messages, see Correct a #N/A error, Correct a #REF! error, and Correct a #VALUE! error.
Use formula auditing to find empty cells and fix broken functions
Typically, a function doesn't work because it references at least one empty cell. You can use the formula auditing tools in Excel to find the empty cell and fix the broken function. The formula auditing tools use arrows and icons to point to the cells from which a function tries to take the data it needs.
Important To follow the steps in this section, you must first enable an error checking option. On the Tools menu, click Options, click the Error Checking tab, and then select the Formulas referring to empty cells check box.
To see the auditing tools in action:
- On the Page Views worksheet, remove the dollar signs from the formula in cell D4.
- Clear cells D5 through D41, and then copy the changed formula down to Cell D41. Because all instances of the formula except the first one contain an error, Excel displays a green triangle in the upper-left corner of each cell:
- Select Cell D5. The Trace Error icon appears.
- Click the arrow to the right of the icon, and then click Trace Empty Cell. The following arrow and icon appear, indicating that the empty cell resides on another worksheet.
- Double-click the black arrow that leads from the icon to the cell.
- Double-click the entry in the Go to dialog box. Excel opens the Pages worksheet because that worksheet contains the empty cell.
At this point, you need to notice something subtle: In the Pages worksheet, Excel highlights the cell range A3 to B40, even though the data only resides in cells A2 to B39. The highlight is showing you that the formula is searching the wrong range of cells. Hence, the error.
To see a more pronounced example of this behavior, go back to the Page Views worksheet, select Cell D10, and repeat Steps 4 through 6. The highlight in the Pages worksheet extends even further down to indicate the greater number of empty cells referenced by the instance of the formula in Cell D10.
Use the Lookup Wizard to save time
If you don't have the time to write a function or if writing functions still frustrates you, you can use the Lookup Wizard. The Lookup Wizard comes with Excel, so you don't need to download it (but Excel may prompt you to install it the first time you try to use it). The wizard uses the INDEX and MATCH functions (instead of VLOOKUP and HLOOKUP) to return records.
Unlike the lookup functions, the INDEX function requires you to specify row and column labels. The INDEX function also returns values from unsorted lists.
The MATCH function determines the row that contains the desired value. You can use INDEX and MATCH to enter more than one search term and return the value that corresponds to the intersection of the two terms. For example, the following table contains shipping data for the first three months of 1994, 1995, and 1996.
Using INDEX and MATCH, you can specify multiple search terms such as "1995" and "March." In that case, the functions would return 52, the value at the intersection of those terms.
The steps in this section explain how to configure Excel to run the Lookup Wizard, and how to use the wizard.
First, let's configure Excel.
- On the Tools menu, click Add-Ins.
- In the Add-Ins dialog box, click Lookup Wizard, and then click OK.
Now let's run the add-in. These steps explain how to duplicate the results you created earlier using the VLOOKUP function. We'll use values from the Page Views and Pages worksheets, and return a result to a blank cell on the Page Views worksheet.
- Open the sample spreadsheet (LookupFunctions.xls) and select the Pages worksheet.
- On the Tools menu, click Lookup. You should see the following text and cell range in the wizard.
If the text seems a bit cryptic, remember that you're defining the location and range of cells through which the function searches. When you see those values, click Next.
- Ensure that Page Name appears in the top drop-down box, and then pick a page ID number from the bottom drop-down box and click Next.
- Do one of the following:
Do one of the following:
- If you want your worksheet to display only the result of your search, click Copy just the formula to a single cell.
- If you want the worksheet to display the search result and the parameters used in the search, click Copy the formula and lookup parameters, and then click Next.
- If you chose to display only the search result, enter a reference to a blank cell in the box, and then click Finish.
- If you chose to display the result and the lookup parameters, enter references to three blank cells, and then click Finish.
The wizard performs the lookup and writes the result or results to the cell or cells you referenced in Step 6.
A final reminder
As you use lookup functions, remember that you're pointing to a data string in one location, telling Excel to find either a partial or absolute match for that data string in another location, and then telling Excel to display a third value that lies either next to or near that second value.
- For more information about using the HLOOKUP and VLOOKUP functions, including code samples, see Help in Excel.
- For more information about using the Lookup Wizard, see the Microsoft Knowledge Base article HOW TO: Use the Excel Lookup Wizard (Lookup.xla) in Excel 2000.
- For more information about the INDEX and MATCH functions used by the Lookup Wizard, see Help in Excel.
About the author
Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, he writes articles and tutorials for Microsoft Data Analyzer.