Page 168 - Excel Workbook for Dummies

17_798452 ch12.qxp 3/13/06 7:33 PM Page 151
Chapter 12: Using the Logical Functions 151
To take advantage of the alternate percentages in this revised Tip Schedule in the fol-
lowing exercise, you need to nest an IF function inside the VLOOKUP function as its
col_index_num argument. This IF function then selects the appropriate column of the
Tip Schedule to use (2 for the 15% Tip amount in column C or 3 for the 20% Tip
amount in column D), depending upon whether the Tip Percentage cell, G2, contains
15% or 20% as its data entry.

Try It

Exercise 12-1: Using the IF Function to Build Formulas that Select Alternate
Values
If Excel is not currently running, launch the program and open the Exercise12-1.xls
workbook file in your Chapter 12 folder in the My Practice Spreadsheets folder on your
hard disk or in the Excel Workbook folder on the workbook CD-ROM. You will use this
expanded version of the Tip Lookup worksheet to practice adding the IF function to a
VLOOKUP function that selects between the 15% or 20% column of the Tip Schedule,
depending upon which percentage is entered into the Tip Percentage cell, G2:

1. Position the cell cursor in cell G2.
This Tip Percentage cell can contain only one of two entries: 15% or 20%. You
will use Excel’s Data Validation feature in this cell to ensure that it can contain no
other data entry.
2. Choose Data➪Validation to open the Data Validation dialog box.
3. Select the List option in the Allow drop-down button and then enter 15% and 20%
in the Source text box separated by a comma (with no spaces, as in 15%,20%)
before you select OK.
When you select the List as the Data Validation option, you can either select the
cell range that contains the allowable data entries in the Source text box or type
in the allowable entries.
4. Click the drop-down button that now appears on the right side of the Tip
Percentage cell, G2, and then select 20% on its drop-down menu.
5. Assign the following range names to the designated cells or cell ranges:
• Tip_percent to cell G2
• Food_total to cell G3
• Tip_table to the cell range B4:D103
6. Position the cell cursor in cell G4.
This is the cell where you will construct your lookup formula using the
VLOOKUP function with an IF function, as its col_index_num argument.
As you may remember from Exercise 11-2, the VLOOKUP function takes the fol-
lowing arguments:
• Lookup_value argument that specifies the cell containing the value you
want looked up in the first column of the vertical lookup table (the cell
named Food_total, in this case)
• Table_array argument that specifies the cell range containing the values
in the lookup table (the cell range named Tip_table, in this case)
• Col_index_num that specifies the number of the column, counting from
left to right, that contains the values you want returned from the lookup