Page 161 - Excel Workbook for Dummies

16_798452 ch11.qxp 3/13/06 7:40 PM Page 144
144 Part II: Using Formulas and Functions
The range name Item_match encompassing C7:C66 is now listed in the
Lookup_value argument text box of the Function Arguments dialog box for
HLOOKUP.
5. Press Tab and then use the Paste Name dialog box to select Price_info 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
prices from the second row (as you count down) of the Price_info range.
7. Press Tab and then type false into the optional Range_lookup argument text box.
You enter FALSE into this argument text box because you only want exact matches
between the bakery items entered into the Item_match range (C7:C66) and the
bakery items entered into the top row of the Price_info range.
Check the Formula Result shown at the bottom of the Function Arguments dialog
box. Because the first bakery item sold in cell C7 of the Daily Sales list is lemon
tarts, the price per dozen returned by the HLOOKUP function from the Price_info
table should be listed as 2.5. If this is the Formula result displayed at the bottom
of your Function Arguments dialog box, proceed to Step 8.
8. Select OK to close the Function Arguments dialog box and insert the formula
with the HLOOKUP function into cell F7.
9. Use the Fill handle on the cell cursor to copy this formula down to the cell range
F8:F66.
Check the prices returned by the copies of the original HLOOKUP formula in the
top rows of the Daily Sales data list against those shown for the various bakery
items in the Price Lookup Table. The price per dozen for the blueberry muffins
should be returned as 3.75, strawberry pie as 8.99, and Lots of Chips cookies
as 1.89.
10. Increase the price per dozen for Lots of chips cookies in cell E4 of the Price
Lookup Table from 1.89 to 3.89.
Note that this change to the basic price is immediately updated in all the sales
of Lots of Chips cookies in the Daily Sales data list. By using a lookup table to
supply the basic price per dozen data to this list, you only need make a single
change to a price in the Price Lookup Table in order to update every single sale
of that item in the entire data list.
11. Save your work in a new workbook named Solved11-1.xls in your Chapter 11
folder inside the My Practice Spreadsheets folder and then close this work-
book file.

Performing a vertical lookup

You use the VLOOKUP function when the data to look up is entered in the first (left-
most) column, arranged sequentially (that is, alphabetically for text entries and from
smallest to largest in the case of numeric entries) by rows from top to bottom. Figure
11-2 shows you just such a vertical lookup table in the form of its Tip Schedule in the
cell range B4:C103 (of which only the first 21 rows are visible in the figure).

The Tip Schedule in the Tip Lookup worksheet is arranged in two columns: Pretax
Total and Tip Amount. Because the Pretax Total column is the first or leftmost column
in this table, it contains the data to look up and match against the Food Total entered
in cell F2 of this spreadsheet. As the lookup column, you note that its values are
arranged in numerical order from smallest to largest.