Microsoft Office Tutorials and References
In Depth Information
Looking It Up
5. Enter the range in which to look for a match.
This can be a range reference or a named area.
6. Optionally, enter a comma ( ,) and enter a –1, 0, or 1 to tell the function
how to make a match.
The default is 1. A 0 forces an exact match.
7. Type a ), and press Enter.
The information returned by MATCH can be helpful when you use it with the
INDEX function. INDEX returns the value found at a specified row and column
intersection within a table. You can use MATCH to find the row and find the
column and then use INDEX to get the actual data.
INDEX takes three arguments:
The table to look in as a range address or range name
The row number relative to the table’s first row
The column number relative to the table’s leftmost column
The return value is the value of the cell where the row and column intersect.
Figure 14-13 shows an example where INDEX retrieves a value from a table
that summarizes some guitar shop sales by product and quarter. The table
range in this example has been named Sales_by_qtr. The following formula,
in cell C2, extracts the sales for 6 Foot Cables for Qtr 2:
=INDEX(Sales_by_qtr, MATCH(“6 Foot Cables”, OFFSET(Sales_
by_qtr,0,0,ROWS(Sales_by_qtr),1),0), MATCH(“Qtr
2”, OFFSET(Sales_by_qtr, 0,0,1,COLUMNS(Sales_
by_qtr))))
Wow, that’s quite a cell full of formula! But you already know everything
you need to understand it. The first argument to INDEX is no mystery — it
is simply the name assigned to the table. The second and third arguments,
which tell INDEX what cell to look in, are complicated. Look at the first one,
for the row argument:
MATCH(“6 Foot Cables”, OFFSET(Sales_by_qtr,
0,0,ROWS(Sales_by_qtr),1),0)
You want to look down the table’s first column, where the product names are
listed, and find the row that contains 6 Foot Cables. You also know that
the MATCH function is just right for this job and that the function needs to
know where to look. In other words, you must tell it the address of the table’s
first column. Here is where OFFSET comes into play:
Search JabSto ::




Custom Search