Microsoft Office Tutorials and References

In Depth Information

**Functions Relevant to Lookups**

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

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.

Functions Relevant to Lookups

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

8-1 lists and describes each of these functions.

Table 8-1:
Functions Used in Lookup Formulas

Function

Description

CHOOSE

Returns a specific value from a list of values (up to 254) supplied as arguments.

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 you specify in the table.

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.

INDEX

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

LOOKUP

Returns a value either from 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.

The examples in this chapter use the functions listed in Table 8-1.

Basic Lookup Formulas

You can use Excel’s basic lookup functions to search a column or row for a lookup value to return

another value as a result. Excel provides three basic lookup functions: HLOOKUP, VLOOKUP, and