As part of the Microsoft Excel development team's ongoing commitment to making the product easier to use and in response to user input, we present the Lookup Wizard. The Lookup Wizard is the first in a series of add-on Wizards being made available to enhance your use of Microsoft Excel. The Lookup Wizard helps you to write formulas that find the value at the intersection of a column and a row in a rectangular range of cells on a worksheet.
For example, if you have a large stock price table with a column for each company and a row of prices for each day over several months, you might want to look up the price of a particular company's stock on a particular day. With the Lookup Wizard, you answer questions about what company you want to look up and what date you're interested in, and the Wizard creates a formula to give you the price.
(Note: To get rich color background, use the Microsoft Internet Explorer version 2.0)
Comp A | Comp B | Comp C | Comp D | Comp E | Comp F | Comp G | |
8/1/95 | 99.45 | 70 | 43.5 | 92 | 20.25 | 43.65 | 49.13 |
8/3/95 | 100 | 50.5 | 44 | 90.12 | 19.6 | 43 | 48.5 |
8/5/95 | 103.5 | 53.25 | 43 | 91.5 | 20 | 43.45 | 48 |
8/7/95 | 102.12 | 55 | 43.12 | 93.5 | 20.75 | 43 | 47.5 |
8/9/95 | 101.5 | 53.25 | 43.75 | 95.75 | 20.25 | 42.3 | 48.13 |
8/11/95 | 100.75 | 50.2 | 44.12 | 95.12 | 20.5 | 41 | 49 |
8/13/95 | 101.25 | 48.75 | 44.5 | 93.25 | 19.8 | 40.75 | 48 |
8/15/95 | 101.75 | 48 | 44.12 | 94 | 19.4 | 42.25 | 48.75 |
8/17/95 | 100.25 | 50 | 43.75 | 94.5 | 20 | 40.75 | 49 |
The result for Comp D on 8/7/95 is 93.5
Comp D | 8/7/95 | 93.5 |
The formula the Lookup Wizard creates is: =VLOOKUP(K16,$B$17:$I$25,MATCH(J16,$B$16:$I$16,))
The Wizard can work with approximate dates, and can even create formulas that let you look up other prices by typing different dates or companies in adjacent cells. You can use the Lookup Wizard whenever you have a list or table of data on a worksheet and want to find specific values in the table. The Wizard takes care of all the details of creating the formulas for you.
The Lookup Wizard can be used with the following Microsoft Excel versions:
The Lookup Wizard add-in file, LOOKUP.XLA, should reside in the Microsoft Excel Library directory. The path to this directory is different depending on which version of Excel you are running.
For standalone Excel 5.0, you will find the LIBRARY directory directly under the EXCEL directory (for example, C:\EXCEL\LIBRARY on a Windows system or Hard Disk:Microsoft Excel 5.0:MACRO LIBRARY on a Macintosh).
For MS Office and Excel 7.0, you will find the LIBRARY directory under the MSOFFICE and EXCEL directories (for example, C:\MSOFFICE\EXCEL\LIBRARY).
To Install and Use the Lookup Wizard
The Microsoft Excel team plans to update this and other wizards on a regular basis. We will make them available in the following ways: