This step-by-step article shows you how to use the Lookup Wizard (Lookup.xla) in Excel 2000.
The Lookup Wizard is part of a series of add-in 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 in a worksheet.
The add-in that is discussed in this article is provided as-is. Microsoft does not guarantee that it can be used in all situations. Although Microsoft Support Professionals can help with the installation and existing functionality of this add-in, they will not modify the add-in to provide new functionality.back to the top How to Use the Lookup Wizard
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 Excel. The following sections describe each step of the wizard. For a detailed example of how to use the wizard, see the Example of the Lookup Wizard
section of this article.back to the top Step 1 of the WizardNOTE
: Before you can use Lookup Wizard, you must install the add-in. On the Tools
menu, click Add-Ins
, and then click Lookup Wizard
The add-in that is discussed in this article can be obtained from the MicrosoftTechNet compact disc and from Online Services.
For additional information about downloading a file from Online Services, click the article number below to view the article in the Microsoft Knowledge Base:
How to Obtain Microsoft Support Files From Online Services
In this step, you need to specify the range of cells that contain data and are to be used for the formula. This range must include column labels androw labels. For the most part, this means that you need to select the entire list on the worksheet.
As with most of the Excel add-ins, the dialog box opens withthe Range Edit
box filled in. Excel (version 5.0 and later) is designed to determine where your list is. If the range is not filled in correctly, you must enter the correct range.NOTE
: It is important to have column and row labels in the data range specified. The wizard uses these labels according to the following steps.back to the top 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 finds. There is a list for both the column and the row labels. Select a label from each list.
In both lists, the first option, (No column label matches exactly
and No row label matches exactly
, respectively) is used to create a new value. Excel uses the largest column or row label less than or equal to the new value. If a new value is created for the column argument, you must select an existing label for the row argument. Likewise, if you create a new value for the row argument, you must select an existing label for the column argument. If you attempt to create a new value for both the column and row arguments, you receive the following message:
You must choose at least one existing value to match.back to the top Step 3 of the Wizard
Your existing value can be a row label or a column label. Please try again.
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. You can choose either of the following options:
back to the top Step 4 of the Wizard
- Copy only the formula to a single cell.
This option copies the formula to the worksheet, and it is selected by default.
- Copy the formula and the 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 go through the wizard again.
The information that is needed in this step depends on which option you chose in step 3 of the wizard. Depending on which option you chose, do one of the following:
back to the top Example of the Lookup Wizard
- If you chose the first option in step 3 (that is, to copy only the formula that was generated from the current settings), you need to provide the cell reference for where the formula is to be placed. Use the mouse to select the cell reference or type it.
- If you chose the second option in step 3 (that is, to 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 three cells.) You can select the cell by using the mouse or you can type the cell reference.
To use the Lookup Wizard, follow these steps:
back to the top
- Type the following into a new worksheet:
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, point to Wizard, and then click Lookup.
- In step 1 of the wizard, the range should be $A$1:$E$10 under Where is the range to search, including the row and column labels?. If the range is not $A$1:$E$10, type the correct range, and then click Next.
- In step 2 of the wizard, select the column label Comp C and the row label 8/11/95 from the lists, and then click Next.
- In step 3 of the wizard, click Copy just the formula in a single cell, and then click Next.
- In step 4 of the wizard, the cell reference in the worksheet in which to copy the formula should be $F$1; if so, click Finish.
The answer in F1 is 44.12, and the formula is as follows: