MS Excel Lookup Wizard
| Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. |
By Michael Meulemans
ABSTRACT: The new Microsoft Excel Lookup Wizard, made available this month on TechNet, allows you to perform lookups on data without having to scroll across lengthy worksheets or write complex lookup formulas with unfamiliar functions.
On This Page
What the Wizard Does
How the Wizard Works
Conclusion
What the Wizard Does
Locating information in lengthy, complex spreadsheets that span multiple screens can be frustrating even for advanced users of Microsoft Excel: you have to scroll up, down, and across screens to locate specific cell values. Excel has hundreds of built-in functions to pinpoint, organize, and analyze data, but many Excel users do not know how to include functions in formulas, which can locate information on a worksheet.
This month the Excel team introduces a new Wizard to help users locate information and write lookup formulas without scrolling or learning the functions VLOOKUP(), HLOOKUP(), and INDEX(MATCH()) used to construct lookup formulas.
How the Wizard Works
Click on the button at the end of this article to install the Wizard and README.TXT file on your hard drive, then follow the instructions in the README.TXT file to make the Wizard accessible through the Excel Tools menu. When you want to create a lookup in a range of cells, select the Wizard and step through its procedure, answering the questions it asks about the table.
Figure 1 provides a very basic example of how the Lookup Wizard can be used. It contains three columns: Date, Code, and Sales Amount. Notice that the dates in the Date column are completely random. Suppose you want to look up the Sales Amount instantly for any given date but you don't want to sort through the Date column, locate the date, and match it with its corresponding Sales Amount?
.gif "Figure 1: : Cells A19, B19, and C19 generated by Excel Lookup Wizard")
Figure 1: : Cells A19, B19, and C19 generated by Excel Lookup Wizard
Click on a cell and select the Lookup Wizard from the Tools menu. Step 1 of the Wizard appears and asks you to specify the table's range (Figure 2).
.gif "Figure 2: : Step 1 of the Lookup Wizard: specifying the table's range")
Figure 2: : Step 1 of the Lookup Wizard: specifying the table's range
If the table range that automatically appears in the text box is correct, select Next and continue. The Wizard asks more questions about the column and row labels, and how and where you want results displayed. If the values that appear with the screens are correct, select Next and move on. The rest of the screens and questions are similar to the Table Range screen.
After you complete the Lookup Wizard's four steps, cells A19, B19, and C19 are generated on the worksheet. The formula for the Lookup value in C19 is also created: =INDEX($A$1:$C$15,MATCH(B19,$A$1:$A$15,),MATCH(A19,$A$1:$C$1,))
Obviously, creating this formula from scratch would be an involved procedure.
Changing the date value in cell B19 automatically updates the Sales Amount value: you don't need to return to the table to locate the information. This is particularly useful when dealing with large, complex worksheets with data spread over several screens: inventory lists, call tracking sheets, product/part number listings, and so on. Multiple columns, which may be difficult to read (such as the Code column in Figure 1), don't interfere with the Wizard. Another advantage is that the Wizard works with approximate dates and, unlike the VLOOKUP() function, does not require the first column of the lookup table to be sorted in ascending order.
To better understand how you can use the Wizard to simplify complicated tasks and for more information on functions, see these Knowledge Base articles.
Article Title |
Article Summary |
|---|---|
Performing a Lookup with Unsorted Data in Excel (PSS ID Number: Q77114) |
The Excel VLOOKUP() function searches the leftmost column of an array for a particular value and returns the corresponding value from a cell in another column, but it requires that the first column of the lookup table be sorted in ascending order. This article describes different formulas you can use to return the same information returned by VLOOKUP() when the first column of the table is not sorted. |
Excel: Finding Exact Match with LOOKUP() Functions (PSS ID Number: Q26503) |
When seeking a value in a range of sorted information, HLOOKUP() and VLOOKUP() return the value of the cell or, if no lookup_value is found, the largest value in the range less than or equal to it. This article describes how to generate an error message if an exact match is not found. |
Excel: Using INDEX and MATCH Against Multiple Criteria (PSS ID Number: 59482) |
This article describes two techniques to retrieve a value based on multiple criteria. This might be used, for example, to retrieve a part price when two fields are necessary to locate it. |
Conclusion
The Lookup Wizard helps Excel users locate information and create lookup formulas quickly. Even expert users will find the Wizard's lookup formulas useful in advanced spreadsheet and application development projects.
Microsoft TechNet
Volume 4, Issue 1
January 1996