Page 160 - Excel Workbook for Dummies

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

Figure 11-1:
The Jan-06
Sales work-
sheet show-
ing a Price
Lookup Table
immediately
above the
Daily Sales
spreadsheet.

In the following exercise, you use the information kept in this Price Lookup Table to
supply the missing information to the Price/Doz column in the Daily Sales data list
below it. To do this, you construct a formula using the HLOOKUP function that
matches the bakery item listed as sold in the Item column of the data list (C7:C66)
against the items shown in the top row of the Price Lookup Table. It then returns the
price per dozen for the matched item to the appropriate cell in the Price/Doz column
in the data list (F7:F66).

Try It

Exercise 11-1: Building Formulas that Perform Horizontal Lookups in a Table
If Excel is not currently running, launch the program. Then, open the Exercise11-1.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
Jan-06 Sales worksheet with the Price Lookup Table and the Daily Sales data list you
need to practice using the HLOOKUP function:

1. Assign the following range names to the designated cell ranges:
• Item_match to cell range C7:C66
• Price_info to cell range C3:F4
2. Position the cell cursor in cell F7 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 HLOOKUP in the list and select OK.
4. Choose Insert➪Name➪Paste and then click Item_match and select OK in the
Paste Name dialog box.