Microsoft Office Tutorials and References

In Depth Information

Chapter 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 bey-

ond 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 related value. A

common telephone directory (remember those?) provides a good analogy: If you want to find a person's tele-

phone 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 neces-

sarily need to be an “official” table, as created by the Excel Insert
⇒
Tables
⇒
Table com-

mand.

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

ployee 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 lookup formulas in the following table use the

VLOOKUP function:

Cell

Formula

D2

=VLOOKUP(C2,EmpData,2,FALSE)