MS Excel: MATCH Function (WS)
In Microsoft Excel, the MATCH function searches for a value in an array and returns the relative position of that item.
Syntax
The syntax for the MATCH function is:
MATCH( value, array, [match_type] )
value is the value to search for in the array.
array is a range of cells that contains the value that you are searching for.
match_type is optional. It the type of match that the function will perform. The possible values are:
| match_type | Explanation |
|---|---|
| 1 (default) | The MATCH function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order.
If the match_type parameter is omitted, the MATCH function assumes a match_type of 1. |
| 0 | The MATCH function will find the first value that is equal to value. The array can be sorted in any order. |
| -1 | The MATCH function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order. |
Note
- The MATCH function does not distinguish between upper and lowercase when searching for a match.
- If the MATCH function does not find a match, it will return a #N/A error.
- If the match_type parameter is 0 and a text value, then you can use wildcards in the value parameter.
| Wild card | Explanation |
|---|---|
| * | matches any sequence of characters |
| ? | matches any single character |
Applies To
- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Type of Function
- Worksheet function (WS)
Worksheet Function Example
Let's take a look at an example to see how you would use the MATCH function in a worksheet:
Based on the Excel spreadsheet above, the MATCH function would return the following:
| =MATCH(10572,A2:A6,1) | would return 3 (it matches on 10571 since the match_type parameter is set to 1) |
| =MATCH(10572,A2:A6) | would return 3 (it matches on 10571 since the match_type parameter has been omitted and will default to 1) |
| =MATCH(10572,A2:A6,0) | would return #N/A (it doesn't find a match since the match_type parameter is set to 0) |
| =MATCH(10573,A2:A6,1) | would return 4 |
| =MATCH(10573,A2:A6,0) | would return 4 |
Let's take a look at how we can use wild cards in the MATCH function.
Based on the Excel spreadsheet above, the MATCH function would return the following:
| =MATCH("A?ples", A2:A6, 0) | would return 1 |
| =MATCH("O*s", A2:A6, 0) | would return 2 |
| =MATCH("O?s", A2:A6, 0) | would return #N/A |
Frequently Asked Questions
Question: In Microsoft Excel, I tried this MATCH formula but it did not work:
=IF(MATCH(B94,Overview!D$54:D$96),"FS","Bulk")
I was hoping for an easier formula than this:
=IF(OR(B94=Overview!D$54,B94=Overview!D55,B94=Overview!D56, {etc thru D96} ),"FS","Bulk")
Answer: When you are using the MATCH function, you need to be aware of a few things.
First, you need to consider whether your array is sorted in a particular order (ie: ascending order, descending order, or no order). Since we are looking for an exact match and we don't know if the array is sorted, we want to make sure that the match_type parameter in the MATCH function is set to 0. This will find a match regardless of the sort order.
Second, we know that the MATCH function will return an #N/A error when a match is not found, so we will want to use the ISERROR function to check for the #N/A error.
So based on these 2 additional considerations, we would want to modify your original formula as follows:
=IF(ISERROR(MATCH(B94,Overview!D$54:D$96,0))=FALSE,"FS","Bulk")
This would check for an exact match in the D54:D96 array on the Overview sheet and return "FS" if a match is found. Otherwise, it would return "Bulk" if no match is found.