Microsoft Office Tutorials and References

In Depth Information

**Chapter 8: Using Lookup Functions**

8

Using Lookup Functions

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

●

This chapter discusses various techniques that you can use to look up a value in a table. Microsoft

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 techniques that go well beyond Excel’s normal lookup capabilities.

What Is a Lookup Formula?

A
lookup formula
essentially returns a value from a table (in a range) by looking up another value.

A common telephone directory provides a good analogy: If you want to find a person’s telephone

number, you first locate the name (look it up) and then retrieve the corresponding number.

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 Excel’s Insert➜Tables➜Table command.

Figure 8-1 shows a simple worksheet that uses several lookup formulas. This worksheet contains

a table of employee data (named
EmpData
), beginning in row 7. 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:

Cell

Formula

D2

=VLOOKUP(B2,EmpData,2,FALSE)

E2

=VLOOKUP(B2,EmpData,3,FALSE)

F2

=VLOOKUP(B2,EmpData,4,FALSE)

G2

=VLOOKUP(B2,EmpData,5,FALSE)