The Lookup Wizard is part of a series of add-on wizards that Microsoft hasmade available to enhance your use of Microsoft Excel. The Lookup Wizardhelps you to write formulas that find the value at the intersection of acolumn and a row in a rectangular range of cells on a worksheet.
The add-in in this article is provided "as is" and Microsoft does notguarantee that it can be used in all situations. Although MicrosoftTechnical Support professionals can help with the installation and existingfunctionality of this add-in, they will not modify the add-in to providenew functionality.
NOTE: The Lookup Wizard is included with Microsoft Excel 97 or MicrosoftExcel 98 Macintosh Edition.
The Lookup Wizard creates lookup formulas that find the value at theintersection of a column and a row. The formulas that the wizard createsuse a combination of the lookup functions that are available in MicrosoftExcel. Below is a detailed description of each step in the wizard,followed by an example.
Step 1 of the Wizard
In this step, you need to specify the range of cells containing data thatwill be used for the formula. This range must include column labels androw labels. For the most part, this means you need to select the entirelist on the worksheet.
Like most of the Microsoft Excel add-ins, the dialog box will open withthe Range Edit box filled in. Microsoft Excel (version 5.0 and later) isdesigned to determine where your list is. If the range is not filled incorrectly, you must select the correct range, either by using the mouse orby typing it in manually.
NOTE: It is important to have column and row labels in the data rangespecified. The wizard uses these labels in the following steps.
Step 2 of the Wizard
The result of the formula that this wizard creates is the intersection ofa row and a column. In this step, you need to specify the row and columnlabels that correspond to the intersection that the wizard will find.There will be a list for each the column and the row labels. Select a labelfrom each list.
In both lists, the first option ("No column label matches exactly" and "Norow label matches exactly") is used to create a new value. Microsoft Exceluses the largest column or row label less than or equal to the new value.If a new value is created for the column argument, then an existing labelmust be chosen for the row argument. Likewise, if a new value is createdfor the row argument, an existing label must be chosen for the columnargument. If you attempt to create a new value for both the columnand row arguments, the following message appears:
You must choose at least one existing value to match. Your existing value can be a row label or a column label. Please try again.
Step 3 of the Wizard
In this step, the wizard copies the formula to the worksheet. There aretwo different ways that the wizard can copy the formula to the worksheet.Use either of the following options:
- Copy only the lookup formula that was generated from the current settings. (Or, Copy just the formula to a single cell.)
This option copies the formula to the worksheet, and it is selected by default.
- Include current lookup parameters in the worksheet, so that they can easily be changed. (Or, Copy the formula and lookup parameters.)
This option copies the formula as well as the values of the lookup parameters to the worksheet. If you use this option, you can change the values of the lookup parameters without having to modify the formula or to go through the Wizard again.
Step 4 of the Wizard
Depending on which option you chose in step 3 of the wizard, theinformation that is needed in this step changes. Depending on which optionyou chose, do either of the following:
- If you chose the first option in step 3 (that is, copy only the lookup formula that was generated from the current settings), you need to provide the cell reference of where the formula is to be placed. You can use the mouse to select the cell reference or you can type it in.
- If you chose the second option in step 3 (that is, include current lookup parameters in the worksheet), you need to provide the cell reference of where the lookup parameter values and formula are to be placed. You need to select only one cell reference because the wizard uses the cell reference specified plus the next two cells in that row. (The wizard uses a total of 3 cells.) You can select the cell using the mouse or you can type in the cell reference.
Example of the Lookup Wizard
NOTE: Before you can use this example, you must install the add-in. Pleasedownload the file and see the Readme.txt file for the installationinstructions. In Microsoft Excel 97 and Microsoft Excel 98 MacintoshEdition, click Add-Ins on the Tools menu and select Lookup Wizard.
The add-in discussed in this article can be obtained from the MicrosoftTechNet compact disc and from Online Services.
For additional information on downloading a file from Online Services,please see the following article in the Microsoft Knowledge Base:
How to Obtain Microsoft Support Files From Online Services
To use the Lookup Wizard, do the following:
- Type the following into a new spreadsheet:
A1: B1: Comp A C1: Comp B D1: Comp C E1: Comp D A2: 8/1/95 B2: 99.45 C2: 70 D2: 43.5 E2: 92 A3: 8/3/95 B3: 100 C3: 50.5 D3: 44 E3: 90.12 A4: 8/5/95 B4: 103.5 C4: 53.25 D4: 43 E4: 91.5 A5: 8/7/95 B5: 102.12 C5: 55 D5: 43.12 E5: 93.5 A6: 8/9/95 B6: 101.5 C6: 53.25 D6: 43.75 E6: 95.75 A7: 8/11/95 B7: 100.75 C7: 50.2 D7: 44.12 E7: 95.12 A8: 8/13/95 B8: 101.25 C8: 48.75 D8: 44.5 E8: 93.25 A9: 8/15/95 B9: 101.75 C9: 48 D9: 44.12 E9: 94 A10: 8/17/95 B10: 100.25 C10: 50 D10: 43.75 E10: 94.5
- On the Tools menu, click Lookup Wizard. (Or, click the Tools menu, point to Wizard, and click Lookup.)
- In step 1 of the wizard, the range $A$1:$E$10 should be filled in. If it is not, type in the correct range. Click Next.
- In step 2 of the wizard, select the Column label "Comp C" and the row label "8/11/95" from the lists. Click Next.
- In step 3 of the wizard, select "Copy just the formula in a single cell." Click Next.
- In step 4 of the wizard, the cell reference on the worksheet to copy the formula to is $F$1. Click the Finish button. The answer in F1 will be 44.12, and the formula will be the following:
=INDEX($A$1:$E$10,MATCH(DATEVALUE("8/11/95"),$A$1:$A$10,), MATCH("Comp C",$A$1:$E$1,))