By Colin Wilcox
If you work with large lists in Excel, you can use lookup functions to retrieve individual records from those lists quickly. This column explains how to use two of the functions: VLOOKUP and HLOOKUP.
|Microsoft Office Excel 2003
Microsoft Excel 2000 and 2002
A friend of mine recently came to me with a problem:
"I'm trying to use the lookup function in Excel, and it's not working," she fumed. "Can you document them for me in plain English?"
My friend manages a large Web site. She uses Microsoft Access to store and manage data about the number of hits that her site receives, and she imports the data into Microsoft Excel for analysis. To make the data easier to find, she places the records in several small worksheets instead of one huge sheet. She'd heard that lookup functions could save time by finding related data in the various sheets.
So, let's start with the basics: You use lookup functions to find related records in large worksheets. When you use a lookup function, you're essentially saying, "Here's a value. Go to another location, find a match for my value, and then show me the words or numbers that reside in a cell that corresponds to that matching value." If it helps, you can think of that third value as your search result.
The tips in this article explain how to use two of the most popular lookup functions: VLOOKUP and HLOOKUP. In the function names, the V stands for vertical and the H stands for horizontal. You use VLOOKUP when you need to search through one or more columns of information, and you use HLOOKUP when you need to search through one or more rows of information.
Use VLOOKUP to search through columns of data
To start, download the Excel 2002 sample file: Lookup Function Sample Data. The file uses fictitious data that demonstrates my friend's problem. The file contains two worksheets: Page Views and Pages. The Page Views sheet contains a set of IDs that uniquely identify each site page, plus information on the number of hits each page received during September 2002. The Pages worksheet contains the page IDs and the names of the pages that correspond to each ID.
The page IDs appear in both worksheets because the source database uses a normalized data structure. In that structure, the IDs enable users to find the data for a given page. For a gentle introduction to normalized data structures, see Design Access databases with normal forms and Excel.
Because the data resides in columns, we'll use the VLOOKUP function to enter a page ID on the first worksheet and return the corresponding page name from the second worksheet. Follow these steps:
- In the Page Views worksheet, click cell E3 and type VLOOKUP.
- In cell E4, type Result.
- Click cell F4 and type this formula in either the cell or the formula bar:
Note #N/A appears in cell F4 because the function expects to find a value in cell F3, but that cell is empty. You'll add a value to cell F3 in the next step. For more information about fixing #N/A errors, see Correct a #N/A error.
- Copy the value from cell A4 into cell F3, and then press ENTER. Home Page appears in cell F4.
- Repeat steps 3 and 4 using the value in cell A5. Comics & Humor appears in cell F4.
Without having to navigate to the second worksheet, you found out which pages receive the majority of visits from site users. That's the value of the lookup functions. You can use them to find records from large data sets with less time and effort.
Understanding the parts of the function
The function that you used in the previous section performed several discrete actions. The following figure describes each action:
The following table lists and describes the arguments that you use with the function. As needed, the information explains how to fix #VALUE and #REF errors that may crop up when you use the functions. You need to know this information to use the function successfully. The HLOOKUP function uses the same syntax and arguments.
||Function name. Like all functions in Excel, you precede the name with an equal sign (=) and place the required information (or, in geek terms, the arguments) in parentheses after the function name. In this case, you use commas to separate all parameters or arguments.
Your search term: the word or value that you want to find. In this case, the search term is the value that you enter into cell F3. You could also embed one of the page ID numbers directly into the function. Excel Help calls this part of the function the lookup_value.
If you don't specify a search value, or you reference a blank cell, Excel displays the #N/A error message.
The range of cells that you want to search. In this case, the cells reside on another worksheet, so the worksheet name (Pages) precedes the range values (A2:B39). The exclamation point (!) separates the sheet reference from the cell reference. If you only want to search through a range residing on the same page as the function, remove the sheet name and exclamation point.
You can also use a named range in this part of the function. For example, if you assigned the name "Data" to a range of cells on the Pages worksheet, you could use 'Pages'!Data. Excel Help calls this part of the function the table_array value.
If you use a range lookup value of TRUE, then you must sort the values in the first column of your table_array argument in ascending order. If you don't, the function cannot return accurate results.
The column in your defined range of cells that contains the values you want to find. For example, column B in the Pages worksheet contains the page names that you want to find. Since B is the second column in the defined range of cells (A2:B39), the function uses 2. If your defined range included a third column, and the values you wanted to find resided in that column, you would use 3, and so on.
Remember that the column's physical position in the worksheet does not matter. If your cell range starts at column R and ends at column T, you use 1 to refer to column R, 2 to refer to column S, and so on.
Excel Help calls this part of the function the col_index_num value. If you use the HLOOKUP function, Excel Help calls this part the row_index_num value, and you follow the same guidelines.
Note If you use the wrong value in this argument, Excel displays an error message. You can make either of these errors:
- If the value is less than 1, Excel displays #VALUE!. To fix the problem, enter a value of 1 or greater. For more information about #VALUE! errors see Correct a #VALUE! error.
- If the value exceeds the number of columns in the cell range, Excel displays #REF! because the formula can't reference the specified number of columns. For more information about fixing #REF errors, see Correct a #REF! error.
Exact match. If you use FALSE, VLOOKUP returns an exact match. If Excel cannot find an exact match, it displays the #N/A error message. For more information about fixing #N/A errors, see Correct a #N/A error.
If you set the value to TRUE or leave it blank, VLOOKUP returns the closest match to your search term. If you set the value to TRUE, you must sort the values in the first column of your table array in ascending order.
Excel Help calls this part of the function the range_lookup value.
General guidelines for using the VLOOKUP function
Keep these rules in mind as you use the VLOOKUP function:
- If you want the function to return exact matches, you must sort the values in your table array in ascending order or the function will fail.
- The function starts searching at the top left of the cell range that you define, and it searches columns to the right of your starting point.
- You must always separate the arguments with comma.
Use the HLOOKUP function to search through rows of data
The steps in the previous section used the VLOOKUP function because the data resided in columns. The steps in this section explain how to use the HLOOKUP function to find data in one or more rows.
- In the Pages worksheet, copy the data in the cell range A2 to B39.
- Scroll to the top of the worksheet, right-click cell D2, and then click Paste Special.
- In the Paste Special dialog box, select Transpose, and then click OK. Excel pastes the data into two rows starting at cell D2 and ending at cell AO3.
- In the Page Views worksheet, type HLOOKUP in cell E6, type Result in cell E7, and then enter this formula into cell F7:
- In cell F6, enter the ID from cell A4 and then press ENTER. Home Page appears in cell F6. You get the same type of result, but you searched through a set of rows instead of columns.
The HLOOKUP function uses the same arguments as the VLOOKUP function. However, instead of declaring the column that contains the values you want to find, you declare the row.
Next, let's look at an important principle that applies to both functions. Go to the Pages worksheet and follow these steps:
- In cells D4 through M4, type anything that comes to mind. You can type anything you want, just add some text or numbers to those cells.
- On the Page Views worksheet, alter the HLOOKUP formula so it reads as follows:
When you finish changing the formula, the value you entered in cell D4 appears. Here's the principle to keep in mind: The value that you want to find does not have to reside in a cell next to your match value. It can reside in any number of columns to the right of your match value, or in any number of rows below your match value. Just make sure that you extend your table_array and col_index_num or row_index_num arguments so that they encompass the values that you want to find.
General guidelines for using the HLOOKUP function
Keep these rules in mind as you use the HLOOKUP function:
- The function starts searching at the top left of the cell range that you define, and it searches the rows below and to the right of your starting point.
- You must always separate the arguments with commas.
- If you want the function to return exact matches, you must sort the values in your data in ascending order. Yes, you can sort horizontally. To do so, follow these steps:
- In the Pages worksheet, click cell D2.
- On the Data menu, click Sort.
- In the Sort dialog box, click Options.
- In the Sort Options dialog box, click Sort left to right, and then click OK.
- In the Sort dialog box, click OK to sort the data.
In the next Power User column
The next Power User column, More ways to use HLookup and VLookup functions, explains how to:
- Use ToolTips to write functions.
- Use a mix of absolute and relative cell references to return multiple records.
- Debug your functions.
- Use the Lookup Wizard. The wizard automates the process of finding data, but it uses the INDEX and MATCH functions instead of the HLOOKUP and VLOOKUP functions.
- For more information about using the VLOOKUP and HLOOKUP functions, including code samples, 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.