XL: Using the Lookup Wizard for Microsoft Excel (Lookup.xla)

ID: Q147255

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

SUMMARY

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 new functionality.

NOTE: The Lookup Wizard is included with Microsoft Excel 97 or Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

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 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, 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:

Q119591 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,)) 
```

Additional query words: 5.00a 5.00c 97 98 XL97 XL98 XL7 XL5

Keywords : kbtool xlformula
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,97; Win95:7.0,7.0a; winnt:5.0
Platform : MACINTOSH Win95 WINDOWS winnt
Issue type : kbhowto