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 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 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.
How to Use the Lookup Wizard
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 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.
Step 1 of the Wizard
NOTE: 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 Microsoft
TechNet 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:
119591�
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 and
row 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 with
the
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.
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 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.
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. You can choose either of the following options:
- 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.
Step 4 of the Wizard
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:
- 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.
Example of the Lookup Wizard
To use the Lookup Wizard, follow these steps:
- 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:
=INDEX($A$1:$E$10,MATCH(DATEVALUE("8/11/95"),$A$1:$A$10,),
MATCH("Comp C",$A$1:$E$1,))