The MATCH function in Excel looks for a value in a range of cells and returns that value's position in the range. So, if you have a list of names and "Jack" is in the 10th cell of the list, the MATCH function would return 10 for "Jack".
The MATCH function in Excel has three arguments (values in the function separated by commas) and is written as follows:
=MATCH(Lookup_Value, Range_to_Search, [Type])
The lookup value can be a number or text, and if it is text MATCH is NOT case-sensitive.
| Type | Description |
|---|---|
| 1 | This is the default match type. This tells MATCH to return the position of the largest value that is NOT greater than the lookup value. Note: values must be arranged from smallest to largest in order for this mode to work. |
| 0 | This tells match to return the position of the exact lookup value. The values do not need to be in any particular order for this mode to work. |
| -1 | This tells MATCH to return the position of the smallest value that is NOT less than the lookup value. Note: values must be sorted largest to smallest for this mode to work. |
If you need an exact match, remember to supply 0 as the third argument!
MATCH returns the position of the values we tell it to search for. Take the following examples with the list of Adam Sandler movies.
| A | B | |
|---|---|---|
| 1 | Movie | Year |
| 2 | Billy Madison | 1995 |
| 3 | Happy Gilmore | 1996 |
| 4 | The Waterboy | 1998 |
| 5 | 50 First Dates | 2004 |
| 6 | Uncut Gems | 2019 |
To get the position of "Happy Gilmore" we would write the following function:
=MATCH("Happy Gilmore", A2:A6, 0)Remember the 0 match type tells Excel to look for an exact match, so it would return 2 because Happy Gilmore is the second movie in the list. MATCH is not case-sensitive, so we could supply "hApPy GiLMoRe" as the lookup value and the function would still return 2.
Notice that these movies are sorted in ascending order (smallest to largest) by year, so that means we can use the 1 match type on the year column. To find the position of the 2004 film, you could use the following function:
=MATCH(2004, B2:B6, 1)This function would return 4. If we were to supply 2005 as the first argument, we would still get 4 because we're using match type 1 and 2004 is the largest value that is not greater than the lookup value.
Because the years are sorted in ascending order, we cannot use match type -1 on the Adam Sandler list. Instead, let's use this list of states with their populations in descending order (largest to smallest).
| A | B | |
|---|---|---|
| 1 | State | Population |
| 2 | California | 39,512,223 |
| 3 | Texas | 28,995,881 |
| 4 | Florida | 21,477,737 |
| 5 | New York | 19,453,561 |
| 6 | Pennsylvania | 12,801,989 |
The -1 match type tells Excel to look for the smallest value that is greater than or equal to the lookup value. Say we want to find the position of the state whose population is no less than 20,000,000.
=MATCH(20000000, B1:B5, -1)Because there is no exact match, the -1 match type tells Excel to look for the smallest value that is greater than or equal to the lookup value, which is 21,477,737, so this function would return 3.
If the lookup value is text and the match type is 0, Excel lets us use wildcards to match strings of text that have specific segments that we specify.
| A | |
|---|---|
| 1 | Indonesia |
| 2 | Thailand |
| 3 | Singapore |
| 4 | Malaysia |
| 5 | Philippines |
| 6 | Vietnam |
| 7 | Cambodia |
| 8 | Brunei |
| 9 | Myanmar |
| 10 | Laos |
For example, say we want to find the position of the first country from the list that begins with "Th". We could use the following function:
=MATCH("Th*", A1:A10, 0)This wildcard would match with "Thailand" so the MATCH function would return 2.
To find the position of the first country that ends with "m", you would use the following:
=MATCH("*m", A1:A10, 0)This function would return 6 for Vietnam.
We could even use wildcards for the beginning and the end. Say we wanted to find the position of the country that had "pp" somewhere in the name.
=MATCH("*pp*", A1:A10, 0)This wildcard would match with "Philippines" so the function would return 5.