The Lookup Wizard is part of a series of add-on wizards that Microsoft has
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.
The add-in in this article is provided "as is" and Microsoft does not
guarantee that it can be used in all situations. Although Microsoft
Technical Support professionals can help with the installation and existing
functionality of this add-in, they will not modify the add-in to provide
NOTE: The Lookup Wizard is included with Microsoft Excel 97 or Microsoft
Excel 98 Macintosh Edition.
The Lookup Wizard creates lookup formulas that find the value at the
intersection of a column and a row. The formulas that the wizard creates
use a combination of the lookup functions that are available in Microsoft
Excel. 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 that
will be used for the formula. This range must include column labels and
row labels. For the most part, this means you need to select the entire
list on the worksheet.
Like most of the Microsoft Excel add-ins, the dialog box will open with
the Range Edit box filled in. Microsoft Excel (version 5.0 and later) is
designed to determine where your list is. If the range is not filled in
correctly, you must select the correct range, either by using the mouse or
by typing it in manually.
NOTE: It is important to have column and row labels in the data range
specified. 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 of
a row and a column. In this step, you need to specify the row and column
labels 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 label
from each list.
In both lists, the first option ("No column label matches exactly" and "No
row label matches exactly") is used to create a new value. Microsoft 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, then an existing label
must be chosen for the row argument. Likewise, if a new value is created
for the row argument, an existing label must be chosen for the column
argument. If you attempt to create a new value for both the column
and 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 are
two 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
- 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, the
information that is needed in this step changes. Depending on which option
you 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. Please
download the file and see the Readme.txt file for the installation
instructions. In Microsoft Excel 97 and Microsoft Excel 98 Macintosh
Edition, click Add-Ins on the Tools menu and select Lookup Wizard.
The add-in discussed in this article can be obtained from the Microsoft
TechNet 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: