Page 159 - Excel Workbook for Dummies

16_798452 ch11.qxp 3/13/06 7:40 PM Page 142
142 Part II: Using Formulas and Functions
The HLOOKUP follows the nearly identical syntax:

HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

The arguments of these two Lookup functions can be explained as follows:

The lookup_value argument designates the range that contains the values or text
to be looked up in the table.
The table_array argument designates the range with the data table you want
looked up in the lookup table as well as the data you want returned from the
lookup table.
The col_index_num argument in the VLOOKUP function designates the number
of the column in the lookup table (starting with 1 for the leftmost column and
increasing one each column to the right) that contains the data you want
returned to the data table.
The row_index_num argument in the HLOOKUP function designates the number
of the row in the lookup table (starting with 1 for the topmost row and increasing
one down each row) that contains the data you want returned to the data table.
The optional range_lookup argument is a TRUE or FALSE value that indicates
whether you want Excel to find an approximate (TRUE or argument omitted) or
exact match (FALSE) to numerical entries in the range designated by the func-
tion’s lookup_value argument.

When using the VLOOKUP and HLOOKUP functions, the text or numeric entries in the
lookup column or row (that is, the leftmost column of a vertical lookup table or the
top row of a horizontal lookup table) must all be unique (no duplicates allowed).
These entries must also be arranged or sorted in ascending order; that is, alphabeti-
cal order for text entries, lowest-to-highest order for numeric entries. (See Chapter 16
for exercises on sorting data in a list or table.)

Performing a horizontal lookup

You use the HLOOKUP function when you’re dealing with a lookup table where the
data to look up is entered in the first (top) row, arranged sequentially (that is, alpha-
betically for text entries and from smallest to largest in the case of numeric entries)
by columns from left to right. Figure 11-1 shows just such a lookup table at the top of
the Jan-06 Sales worksheet — the Price Lookup Table in the cell range C3:F4.

First off, note that bakery items listed in the top, lookup row of this Price Lookup
Table are text values arranged in alphabetical order from left to right as follows:

Blueberry muffins in cell C3
Lemon tarts in cell D3
Lots of Chips cookies in cell E3
Strawberry pie in cell F3

Second, note that the price per dozen for each bakery item is listed in a correspon-
ding column immediately below in the second row of the table (cell range, C4:F4). The
order of the values in the cells in this row is dictated entirely by the order of their
associated bakery items in the row above.