Leading the way in Microsoft Office Development

What is the Lookup Wizard ?

The Lookup Wizard will create a formula that will cross reference a particular column and row from a table.

This formula is a combination of the INDEX() and MATCH() functions.

This is an additional add-in that can be loaded when necessary. It is not loaded by default.

Installing the Add-in

Before you can use this add-in you must install it. This can be done by selecting (Tools > Add-ins).

Select the add-in called Lookup Wizard and press OK to install it.

 (Tools > Add-ins) dialog box

When this add-in is installed an additional "Lookup…" command will be added to the bottom of your Tools drop-down menu.

Width=0;Height=0

Step 1 - Data Table

Lets you identify the table of data. This must include all the column and row headings.

Select (Tools > Lookup) to display the Lookup Wizard dialog box.

Step 2 - Column and Rows Labels

Lets you select which column and row labels correspond to the intersection that you want to return.

There is a list of column labels at the top and a list of row labels at the bottom.

No column label matches exactly - Allows you to enter a new column label.

Excel will then find the largest column label less than or equal to this new value.

No row label matches exactly - Allows you to enter a new row label.

Excel will then find the largest row label less than or equal to this new value.

Step 3 - Display Result

Lets you choose how you want the formula added to the worksheet.

Copy just the formula to a single cell - Copies just the formula to a single cell on the worksheet.

Copy the formula and lookup parameters - Copies the formula as well as the two lookup parameters to the worksheet. This allows you to quickly change the lookup parameters without having to modify the formula.

Step 4 - Location

Lets you select the single cell to identify which cell you want the formula inserted into.

If you choose "copy the formula and parameters" option in step 3 then the two parameters will be added to the next two cells in the same row.

Things to Remember

You cannot choose to enter a new column label and a new row label. You must choose at least one existing value that matches.

Once the wizard has been used to create the formula you cannot use the Wizard to change the formula at a later date.