Page 165 - Excel Workbook for Dummies

16_798452 ch11.qxp 3/13/06 7:40 PM Page 148
148 Part II: Using Formulas and Functions
The Step 2 of 4 Lookup Wizard dialog box now appears, asking you to identify by
its heading the column and the row that contain the data to look up.
4. Leave Apr-06 selected in the Column Label drop-down list box and Part 100
selected in the Row Label drop-down list box and then select the Next button.
The Step 3 of 4 Lookup Wizard dialog box now appears, asking you to select the
form in which the Wizard displays the result. By default, Excel just copies the
formula to a cell in the worksheet, but you can also have the program copy the
lookup parameters; that is, the column lookup value and the row lookup value
along with the lookup formula it constructs.
In this case, this latter option is exactly the one you want to use because it
enables you to change these lookup parameters in the worksheet later on and
have the lookup formula find a new result based on these new values.
5. Select the Copy the Formula and Lookup Parameters option button selected and
then select the Next button.
Excel displays the Step 4 of 6 Lookup Wizard dialog box, asking you to identify
the cell where you want to copy the date, 4/1/2006.

6. Click the text box and then select cell C12 in the Production-06 worksheet
(‘Production-06’!$C$12 appears in the text box) and then select the Next button.
Excel displays the Step 5 of 6 Lookup Wizard dialog box, asking you to identify
the cell where you want to copy the part number, Part 100.

7. Click the text box and then select cell C11 in the Production-06 worksheet
(‘Production-06’!$C$11 appears in the text box) and then select the Next button.
Excel displays the Step 6 of 6 Lookup Wizard dialog box, asking you to identify
the cell where you want to copy the lookup formula.
8. Click the text box and then select cell C13 in the Production-06 worksheet
(‘Production-06’!$C$13 appears in the text box) and then select the Finish button.
Excel returns the result 500 to the Quota cell, C13.
9. Position the cell cursor in cell C13 and then examine the formula Excel entered
there on the Formula bar.
Note that Excel has constructed a formula using an INDEX function with two
nested MATCH functions as its arguments. Both of these Reference functions
are needed in order to perform a two-way lookup in a table.
10. Change the Part Number in cell C11 to Part 102.
As soon as you change the Part Number to Part 102, Excel recalculates its lookup
formula and returns 350 to the Quota cell, C13 (you can check that this is the
correct value in the 2006 Production Schedule table above).
11. Change the Date in cell C12 by entering Sep-2006 in this cell.
As soon as you change the Date Number to Sep-06 in this cell, Excel recalculates
its lookup formula and returns 378 to the Quota cell, C13.
12. Save your changes to the Production-06 worksheet in a new workbook named
Solved11-3.xls in your Chapter 11 folder in the My Practice Spreadsheets folder
and then close this workbook file and exit Excel.