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)

Search JabSto ::

Custom Search