Page 162 - Excel Workbook for Dummies

16_798452 ch11.qxp 3/13/06 7:40 PM Page 145
Chapter 11: Using the Lookup Functions 145

Figure 11-2:
The Tip
Lookup
worksheet
with Tip
Schedule
for looking
up the tip
amount
based on
the pretax
food total.

In the following exercise, you construct a formula using the VLOOKUP function that
returns the tip amount to cell F3 from the Tip Amount column of the Tip Schedule
table based on the Food Total entered into cell F2 and matched against the amounts
listed in the Pretax Total column. Note that this represents a situation where you do
not want Excel to use exact matching because the amount entered into the Food Total
cell can often fall between the whole dollar amounts listed in the Pretax Total column
of the Tip Schedule. When this happens (and you don’t specify FALSE as the optional
range_lookup argument in the VLOOKUP function), Excel returns the amount from the
row above.

Try It

Exercise 11-2: Building Formulas that Perform Vertical Lookups in a Table
Open the Exercise11-2.xls workbook file in your Chapter 11 folder inside the My
Practice Spreadsheets folder or in the Excel Workbook folder on the workbook
CD-ROM. This workbook contains the Tip Lookup worksheet with the Tip Schedule
that you need to practice using the VLOOKUP function:

1. Assign the following range names to the designated cells and cell ranges:
• Food_total to cell F2
• Tip_table to the cell range B4:C103
When naming the Tip_table range, try using the AutoSelect feature to select the
cell range B4:C103 in a couple of clicks.
2. Position the cell cursor in cell F3 and then click the Insert Function button on the
Formula bar.
3. Select Lookup & Reference in the Select a Category drop-down list box, and then
click VLOOKUP in the list and select OK.