Excel MATCH Function

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".

Syntax of the MATCH Function

The MATCH function in Excel has three arguments (values in the function separated by commas) and is written as follows:

=MATCH(LookupValue, Rangeto_Search, [Type])

Lookup Value is the value whose position you are looking for. Lookup Value is required.
Range to Search is the group of cells that you want MATCH to search for the lookup value. This argument is also required.
Match Type is an optional argument. The default value is 1, so if you omit this argument the search type will be 1 (see table below for the different match types).

The lookup value can be a number or text, and if it is text MATCH is NOT case-sensitive.

Match Types

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!

Excel MATCH Function Examples

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.

Wildcard Matching

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.

Continue to MATCH practice exercises! →

Images Referenced

https://excelexercises.com/logo2.png
https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png