Chapter 8: Using Lookup Functions

 Download CD Content

This chapter discusses various techniques that you can use to look up a value in a table. 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.

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 B2, lookup formulas in C2:F2 retrieve the matching information from the table. The following lookup formulas use the VLOOKUP function.

Figure 8-1: Lookup formulas in row 2 look up the information for the employee name in cell B2.

Open table as spreadsheet

Cell	Formula
C2	=VLOOKUP(B2,EmpData,2,FALSE)
D2	=VLOOKUP(B2,EmpData,3,FALSE)
E2	=VLOOKUP(B2,EmpData,4,FALSE)
F2	=VLOOKUP(B2,EmpData,5,FALSE)

This particular example uses four formulas to return information from the EmpData range. In many cases, you'll only want a single value from the table, so use only one formula.