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
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.
Search JabSto ::

Custom Search