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: