Doing a Two-Way Lookup in a Data Table

You can use the Lookup Wizard add-in to build the necessary formulas to do a two-way lookup in a data table. After you activate the Lookup Wizard add-in by selecting the Lookup Wizard check box in the Add-Ins dialog box (Tools rarr; Add-Ins), the program adds a Lookup menu item to the Tools menu on the menu bar.

To better understand how the Lookup Wizard works, for example. You want to create a formula that will look up and return the number produced for a particular part in a particular month. To do this, you must fashion a formula that combines the abilities of both the VLOOKUP function, which can look up the correct row based on the part number you give, and the HLOOKUP function, which can look up the correct column based on the month you enter.

This is exactly what the Lookup Wizard can do for you. To see how, follow along with these steps:

1. Choose Tools → Lookup to open the Lookup Wizard - Step 1 of 4 dialog box.
2. Select the cell range containing the data table in which the lookup is to be performed.
3. When you're finished selecting the cell range, click the Next button to open the Lookup Wizard - Step 2 of 4 dialog box.
   This dialog box is where you select the column and then the row containing the value you want looked up in the data table.
4. In the Which Column Contains the Value to Find drop-down list, click the name of the column containing the value you want to look up.
5. In the Which Row Contains the Value to Find drop-down list, click the name of the row containing the value you want to look up.
6. When you're finished selecting the row and column, click the Next button to open the Lookup Wizard - Step 3 of 4 dialog box.
7. Choose how you want the wizard to display the result:
   • Copy Just the Formula to a Single Cell:
     Select this option button to have only the formula inserted into a cell.
   • Copy the Formula and Lookup Parameters:
     Select this option button to have the column and row information copied along with the formula.
   For this example, I selected the Copy the Formula and Lookup Parameters option button to have Excel copy both the date and the part number into the worksheet above the lookup formula that it creates.
8. Click the Next button to open the Lookup Wizard - Step 4 of 4 or Step 4 of 6 dialog box (depending upon which option you selected in Step 7).
   If you selected the Copy Just the Formula to a Single Cell option button in Step 8, Excel displays the Lookup Wizard - Step 4 of 4 dialog box where you indicate the cell where the formula is to be copied. In this case, skip ahead to Step 11.
   If you selected the Copy the Formula and Lookup Parameters option button in Step 8, the Lookup Wizard - Step 4 of 6 dialog box appears. In this case, continue to Step 9.
9. Type the address of the cell where you want the column parameter copied or click the cell directly in the spreadsheet and then click the Next button.
   When you click Next, Excel displays the Lookup Wizard - Step 5 of 6 dialog box.
10. Type the address of the cell where you want the row parameter copied or click the cell directly in the spreadsheet and then click the Next button.
    When you click Next, Excel displays the Lookup Wizard - Step 6 of 6 dialog box.
11. Type the address of the cell where you want the lookup formula or click the cell directly in the spreadsheet and then click the Finish button.