Page 163 - Excel Workbook for Dummies

16_798452 ch11.qxp 3/13/06 7:40 PM Page 146
146 Part II: Using Formulas and Functions

4. Choose Insert➪Name➪Paste and then click Food_total and select OK in the
Paste Name dialog box.
The range name Food_total, the name assigned to cell F2, is now listed in the
Lookup_value argument text box of the Function Arguments dialog box for
VLOOKUP.
5. Press Tab and then use the Paste Name dialog box to select Tip_table and insert
its name into the Table_array argument text box.
6. Press Tab and then type 2 into the Row_index_num argument text box.
You enter 2 for this value because you want Excel to return the appropriate tip
amounts from the second column (as you count from left to right) of the Tip_
table range.
Note that you omit the optional range_lookup argument for this particular
VLOOKUP function because you want Excel to return a tip amount even when
the program doesn’t find an exact match between the amount in the Food_total
cell and the whole dollar amounts listed in the Pretax Total column.
The Formula result shown at the bottom of the Function Arguments dialog box
is a perfect example of this situation. Currently, the Food_total cell F2 contains
$9.33. When Excel matches this in Pretax Total column of the Tip Schedule, it
does not find an exact match. In this case, it returns 1.35 as the formula result,
the tip amount for a pretax total of 9.00 in row 13 of the Tip Schedule.
When you don’t use exact matching for numerical values, Excel always selects
the value from the row in the table_array argument in a VLOOKUP function or
the column in an HLOOKUP function whose value is closest but doesn’t exceed
the value specified by the lookup_value argument.
7. Select OK to close the Function Arguments dialog box and insert the formula
with the VLOOKUP function into cell F3.
8. Change the Food Total value in cell F2 from $9.33 to $87.20.
The moment you complete the edit in this cell, Excel returns a new tip amount
of $13.05 to cell F2 (this tip amount is 15% of 87.00, the nearest value in the Tip
Schedule that does not exceed the Food Total value).
9. Save your changes to the Tip Lookup worksheet in a new workbook named
Solved11-2.xls in your Chapter 11 folder in the My Practice Spreadsheets folder
and then close the workbook file.

Using the Lookup Wizard

The HLOOKUP and VLOOKUP functions are just fine when you only need Excel to
look up a single value in a horizontal or vertical lookup table and then return the
nearest or exact match based on that single value. Sometimes, however, you need
Excel to perform a two-way lookup, whereby the program returns a value from a data
table based on both a lookup value in its top row as well as a lookup value in its left-
most column.

When such a need arises, it’s time to call upon the Lookup Wizard. This nifty add-in
enables you to perform two-way lookups in a table without having to worry about
constructing the final complex formula with its required INDEX and MATCH Reference
functions.