Page 158 - Excel Workbook for Dummies

16_798452 ch11.qxp 3/13/06 7:40 PM Page 141

Chapter 11

Using the

Lookup Functions

In This Chapter
Doing a horizontal lookup in a data table
Doing a vertical lookup in a data table
Looking up values in a data table using the Lookup Wizard

he Lookup functions in the Lookup & Reference category of Excel functions are designed
Tto automate the process of matching values in two separate lists or tables in a work-
book and then returning a related value. For example, you can set up a price lookup table
in a worksheet where you store and update the prices for all the items your company sells.
After that, you construct formulas in a sales table using the appropriate lookup function
that match an item number entered into a field in the sales table with an item number
entered into the price lookup table. When the function finds a match between these item
numbers, Excel then copies the price associated with that item number in the price lookup
table and pastes it into the appropriate field in the sales table.

The Reference functions are primarily designed to return specific types of information
about particular cells or regions of a worksheet. This part of the Lookup & Reference cate-
gory also includes functions that create hyperlinks to different worksheets and documents
and that transpose the data in a table so that data that originally ran across the rows now
runs down the columns, and vice versa.

In this chapter, you get a chance to practice creating formulas that automate table lookup,
including looking up a single value, either across a row of a lookup data table or down one
of its columns, as well as using the Lookup Wizard to perform a lookup that uses two values
to find the matching data in a lookup data table.

Returning Single Values from a Lookup Table

The most popular of the Lookup & Reference functions are the HLOOKUP (for Horizontal
Lookup) and VLOOKUP (for Vertical Lookup) functions. The VLOOKUP function searches
vertically (top to bottom) the leftmost column of a lookup table until the program locates a
value that matches or exceeds the one you are looking up. The HLOOKUP function searches
horizontally (left to right) the topmost row of a lookup table until it locates a value that
matches or exceeds the one you’re looking up.
The VLOOKUP function uses the following syntax:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])