Excel 2019 Bible

315

C H A P T E R

14

Using Formulas for Matching

and Lookups

IN THIS CHAPTER

Introducing formulas that look up values in a table

Identifying the worksheet functions used to perform lookups

Delving into more sophisticated lookup formulas

T

his 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.

In this chapter you’ll explore 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 tele-

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

We use the term table to describe any rectangular range of data. The range does not necessarily need to be an “offi-

cial” table, as created by choosing Insert ➪ Tables ➪ Table.

Several Excel functions are useful when writing formulas to look up information in a table.

Table 14.1 describes these functions.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.