Microsoft Office Tutorials and References

In Depth Information

**Using lookup functions**

Using lookup functions

You can create formulas using the INDEX and MATCH functions that pinpoint specific

values in tables of data. Typically, a lookup formula includes two MATCH functions: one to

identify a row, and one to indentify a column. The INDEX function takes these values and

returns the contents of the cell at the specified location.

Versions of Excel prior to the 2010 release included the Lookup Wizard, an add-in that

helps to construct the necessary formulas using the INDEX and MATCH functions. These

formulas still work perfectly, in case you still have some of these wizard-generated models

in your workbooks.

For details about the INDEX and MATCH functions, see “Understanding lookup and reference

functions” in Chapter 14.

As you can see in the formula bar in Figure 12-42, there are two MATCH functions at work

within the INDEX function; the first locates the row, and the second locates the column.

They do so using the values in cells P5 and Q5, respectively, containing column and row

criteria you specify. The INDEX function then takes these criteria and returns the value found

at the intersection of the designated row and column.

Figure 12-42
Locate values in tables using the INDEX and MATCH functions.

You’ll find the Lookup Functions.xlsx file with the other examples on the companion

website.

In the previous three figures, you’ll notice a mixture of relative and absolute references in the

formulas. (A row number or column letter preceded by a dollar sign is an absolute reference.)

These are generally used to allow the copying of formulas to other locations while

maintaining the proper relationships. For details, see “Understanding relative, absolute, and mixed

references” back near the beginning of this chapter.