Monday, December 29, 2008

Using the Lookup Wizard (Part 3)

When you use the Lookup Wizard to identify the value in a cell at the intersection of a specific row and column, Excel offers you the choice of either entering just the calculation in your worksheet, or entering both the calculation and the criteria you selected for the calculation into your worksheet. If you choose to enter the criteria as well as the calculation, Excel prompts you individually for cells in which to store each criterion, as well as the result of the calculation. You can place the information anywhere you want in the worksheet.
When you specify locations for the criteria and the results of the calculation, you can collapse the Lookup Wizard dialog boxes, or you can simply click a cell in
your worksheet without collapsing any of the dialog boxes.
Once you complete the Lookup Wizard dialog boxes, Excel enters a formula that combines the INDEX and MATCH functions. You can read more about using these functions individually and together in the section, “Look Up a Value in a Table.” The Lookup Wizard produces the same results as manually combining these functions; however, the technique provided by the Lookup Wizard is easier than typing the functions.

Excel redisplays the Lookup Wizard - Step 4 of 6 dialog box with the address of the cell you selected.

13 Click Next.
The Lookup Wizard - Step 5 of 6 dialog box appears.
14 Repeat Steps 10 to 12 to select a cell to hold one of the labels that describe the value.
15 Click Next. The Lookup Wizard - Step 6 of 6 dialog box appears.
16 Repeat Steps 10 to 12 to select a cell to hold one of the labels that describe the value.
17 Click Finish. Excel redisplays your worksheet.
18 Click the cell containing the formula.
• The result of the formula appears in the worksheet.
• The formula appears in the Formula bar.

TIPS

Why do the values in my columns appear instead of the labels when I use the Lookup Wizard?

When you specified the range that contains the information you want Excel to search in the Lookup Wizard - Step 2 of 4 dialog box, you did not include the labels that probably appear in the first row of your range. If you rerun the wizard and include the labels in your selection, you will not see values in the columns in the Lookup Wizard - Step 2 of 4 dialog box.
Instead, you will see labels. The same principle applies to any row headings that
you may have; be sure to include them in the range to search.

When I use the Lookup Wizard, must the range I search contain numbers?

No. The range can contain numbers or text. The Lookup Wizard returns whatever
you have stored in the cell it identifies as the intersection of the row and column
that you identify when you use the wizard.
For example, if the cell contains “Monday,” then the Lookup Wizard displays “Monday” in the cell you specify.

What happens if the cell for which I search is blank?
Excel returns zero as the result of the Lookup Wizard. The function that appears
in the Formula bar appears in the same format as the function you see as the result of the Lookup Wizard; if you type text or a value into the blank cell, Excel updates the results of the Lookup Wizard.

No Comment

Post a Comment

Other References

@ All Right Reserved 2008. Edit by ExcelPlus Admin Design by Usuário ^