Page 316 - Excel 2007 Bible

20_044039 ch15.qxp 11/21/06 11:07 AM Page 273
Creating Formulas
That Look Up Values
his chapter discusses various techniques that you can use to look up a
value in a range of data. Excel has three functions (LOOKUP, VLOOKUP, IN THIS CHAPTER
Tand HLOOKUP) designed for this task, but you may find that these func- An introduction to formulas that
tions don’t quite cut it.
look up values in a table
This chapter provides many lookup examples, including alternative techniques
that go well beyond the Excel program’s normal lookup capabilities. An overview of the worksheet
functions used to perform
lookups
Introducing Lookup Formulas Basic lookup formulas
A lookup formula essentially returns a value from a table by looking up another More sophisticated lookup
related value. A common telephone directory provides a good analogy. If you formulas
want to find a person’s telephone number, you first locate the name (look it up)
and then retrieve the corresponding number.
NOTE I use the term table to describe a rectangular range of data.
NOTE
The range does not necessarily need to be an “official” table,
as created by Excel’s Insert ➪ Tables ➪ Table command.
Figure 15.1 shows a simple worksheet that uses several lookup formulas. This
worksheet contains a table of employee data, beginning in row 7. This range is
named EmpData. When you enter a last name into cell C2, lookup formulas in
D2:G2 retrieve the matching information from the table. The following lookup
formulas use the VLOOKUP function:

D2 =VLOOKUP(C2,EmpData,2,FALSE)
E2 =VLOOKUP(C2,EmpData,3,FALSE)
F2 =VLOOKUP(C2,EmpData,4,FALSE)
G2 =VLOOKUP(C2,EmpData,5,FALSE)

273