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