Microsoft Office Tutorials and References

In Depth Information

**Looking It Up**

MATCH and INDEX

The MATCH function returns the relative row number or column number of a

value in a table. The key point here is that MATCH returns the relative value

but does not return the value itself.

This function is useful when you need an item’s position. You are not often

interested in this information by itself but may use it in a more complex

formula. I’ll show you how shortly.

MATCH takes three arguments:

✓
The value to search for:
This can be a number, text, or a logical value.

✓
Where to look:
This is a range spanning a single row or column, or a

named area that comprises a single row or column.

✓
How the match is to be applied:
This argument is optional.

The third argument can be one of three values. They work as follows:

✓
1 tells MATCH to find the largest value that is less than or equal to the

lookup value. The array must be sorted in ascending order. This is the

default value if the argument is omitted.

✓
–1 tells MATCH to find the smallest value that is greater than or equal to

the lookup value. The array must be sorted in descending order.

✓
0 tells MATCH to find the first value that is an exact match. The array

need not be sorted.

Figure 14-12 shows the products and revenue for the guitar shop. Note that

the information has been sorted in ascending order according to the Amount

column. The goal is to get a count of how many products have sales less than

$10,000. MATCH makes this easy, as shown in Figure 14-12. This formula is in

cell B4: =MATCH(10000,OFFSET(Sales,0,3,ROWS(Sales),1))-1.

Take this formula apart from the inside out. First, you know that MATCH

needs a reference to the column where it is to search — in this case, the

Amount column in the Sales range. Sounds like a job for OFFSET! Type the

following:

OFFSET(Sales,0,3,ROWS(Sales),1)