HOW TO: Use the Excel Lookup Wizard (Lookup.xla) in Excel 2000

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

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:

1. 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
   					

2. On the Tools menu, point to Wizard, and then click Lookup.
3. 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.
4. 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.
5. In step 3 of the wizard, click Copy just the formula in a single cell, and then click Next.
6. 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,))