Excel 2019 Bible

Part II: Working with Formulas and Functions

TA B L E 14 .1 Functions Used in Lookup Formulas

Function Description

CHOOSE Returns a specific value from a list of values supplied as arguments.

HLOOKUP Horizontal lookup. Searches for a value in the top row of a table and returns a

value in the same column from a row you specify in the table.

IF Returns one value if a condition you specify is TRUE and returns another value if

the condition is FALSE.

IFERROR If the first argument returns an error, the second argument is evaluated and

returned. If the first argument does not return an error, then it is evaluated and

returned.

INDEX Returns a value (or the reference to a value) from within a table or range.

LOOKUP Returns a value from either a one-row or one-column range. Another form of

the LOOKUP function works like VLOOKUP but is restricted to returning a value

from the last column of a range.

MATCH Returns the relative position of an item in a range that matches a specified

value.

OFFSET Returns a reference to a range that is a specified number of rows and columns

from a cell or range of cells.

VLOOKUP Vertical lookup. Searches for a value in the first column of a table and returns a

value in the same row from a column that you specify in the table.

Leveraging Excel’s Lookup Functions

Finding data in a list or table is central to many Excel formulas. Excel provides several

functions to assist in looking up data vertically, horizontally, left to right, and right to

left. By nesting some of these functions, you can write a formula that looks up the correct

data even after the layout of your table changes.

Let’s take a look at some of the more common ways to utilize Excel’s lookup functions.

This book’s website, http://www.wiley.com/go/excel2019bible, includes a copy of the sample

workbook for this chapter. The file is named Performing Lookups.xlsx.

Looking up an exact value based on a left lookup column

Many tables are arranged so that the key piece of data, the data that makes a certain row

unique, is in the far-left column. While Excel has many lookup functions, VLOOKUP was

designed for just that situation. Figure 14.1 shows a table of employees. We want to fill out

a simplified paystub form by pulling the information from this table when an employee’s ID

is selected.