Excel 2010 Bible

309

CHAPTER

Creating Formulas

That Look Up Values

IN THIS CHAPTER

An introduction to formulas

that look up values in a table

An overview of the worksheet

functions used to perform

lookups

Basic lookup formulas

More sophisticated lookup

formulas

T

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, and HLOOKUP) designed for this task, but you may find
that these functions don’t quite cut it.

This chapter provides many lookup examples, including alternative tech-
niques that go well beyond the Excel program’s normal lookup capabilities.

Introducing Lookup Formulas ..........................................................................................

A lookup formula essentially returns a value from a table by looking up
another related value. A common telephone directory provides a good anal-
ogy. If you 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. The range does not
necessarily need to be an “official” table, as created by choosing Insert ➪
Tables ➪ Table. n

Figure 14.1 shows a 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 formu-
las in D2:G2 retrieve the matching information from the table. If the last
name does not appear in Column C, the formulas return #N/A.