You can use the MATCH function to find the row or column position number in a range for a particular value. The MATCH function does not return a value; instead, it returns the relative position of a value in a range. Because the MATCH
function does not return an actual value, it is not often used by itself. However, the MATCH function is often used in conjunction with the INDEX function to return the value at a particular position in a range.
Used in combination, these two functions can help you perform a two-way lookup. That is, you can search down columns and across rows of a range and
return the value in a single cell within that range.
The MATCH function requires two arguments, and you can specify an optional third argument. You must define the cell containing the value for which you want to search and the range to search. When searching a column, you specify only the column you want to search. When searching a row, you specify only the row you want to search.
You can set the Match-type argument to one of three values: 1, 0, or -1. If you omit this argument, Excel sets the value to 1.
Determine the Position of a Value
1 In your worksheet, list the values that Excel should search in rows.
2 Click the cell in which you want to store the function.
3 Click the Formulas tab.
4 Click Insert Function. The Insert Function dialog box appears.
5 Type a description here of the function you want to use. This example uses match.
6 Click Go.
7 Click MATCH.
• A description of the selected function appears here.
8 Click OK. The Function Arguments dialog box appears.
9 Click here and click the cell containing the search value.
The selected cell’s address appears in the Lookup_value field.
10 Click here and select the range containing the values to search.
The selected range appears in the Lookup_array field.
11 Type the value for the Match_type argument. This example uses 0.
12 Click OK.
• Excel displays the result of the formula in the cell you selected in Step 2 as
a serial number.
• The formula appears in the Formula bar.
TIPS
What happens when I omit the Match_type argument or set it to 1?
When you omit the Match_type argument or set it to 1, Excel finds and
returns the location of the largest value that is less than or equal to the
lookup value — the first argument of the MATCH function. To use this
setting, you must sort the values in the range in descending or reverse
alphabetical order.
What happens when I set the Match_type argument to 0?
When you set the Match_type argument to 0, Excel searches for and returns the location of the first value that exactly equals the lookup value. If you use this setting, the order of the values in the range that you search do not matter; they do not need to be in ascending or descending order.
What happens when I set the Match_type argument to -1?
When you set the Match_type argument to -1, Excel searches for and
returns the location of the smallest value in the range that is greater than or
equal to the Lookup Value argument. To use this setting, you must sort the
values in the range in descending or reverse alphabetical order.
The Function Arguments dialog box appears.
No Comment