Chapter 14: Creating Formulas That Look Up Values

In This Chapter

Introducing formulas that look up values in a table

Identifying the worksheet functions used to perform lookups

Getting acquainted with basic lookup formulas

Delving into more sophisticated lookup formulas

This chapter discusses various techniques that you can use to look up a value in a range of data. Excel has three worksheet 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 the Excel program's normal lookup capabilities.

Introducing Lookup Formulas

A lookup formula returns a value from a table by looking up another related value. A common telephone directory (remember those?) 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 any rectangular range of data. The range does not necessarily need to be an “official” table, as created by choosing Insert ⇒ Tables ⇒ Table.

Figure 14.1 shows a worksheet that uses four 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 formulas in D2:G2 retrieve the matching information from the table. If the last name does not appear in Column C, the formulas return ...

Get Excel 2013 Bible now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.