Lookup Wizard

Removed in 2010
In 2010 this add-in was removed so these combinations must be entered manually if required.
The Lookup Wizard will help you to create formulas 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.

Add-in Location

Excel 2007 - C:\Program Files\Microsoft Office\Office12\Library\LOOKUP.XLA
Excel 2003 - C:\Program Files\Microsoft Office\Office11\Library\LOOKUP.XLA
Excel 2002 - C:\Program Files\Microsoft Office\Office10\Library\LOOKUP.XLA

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.

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

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

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

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

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

Important

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.