Excel MATCH function

How to use the Excel MATCH function

Description

The Excel MATCH function searches for a specific item in a given range and returns the position as an index number. MATCH is covers exact, approximate, and wildcard matching. It is frequently used together with the INDEX function for powerful and version-independent lookup operations.

Syntax

MATCH( lookup_value, lookup_array, [match_type] )

Arguments

lookup_value – The value you want to search for 

lookup_array – A range of cells or array

match_type – [Optional] A value indicating which match_type to use

Return value

An integer number representing the position of the lookup_value

How to use the MATCH function in Excel

The Excel MATCH function is used to find out the position of a lookup_value in a lookup_array. MATCH is frequently used together with the INDEX function to perform powerful lookups. By default, it performs an exact match search, like in the example above.

To find out the position of “Joe” (entered as lookup_value in cell D6) in the given vertical list of names, we use the following formula:

= MATCH(D6, B6:B15, 0)    // returns row index 6

MATCH is able to apply three different match modes, which are specific in the match_type argument. In the above statement, we used the exact match type (0). Here you have a list of all keys that you can use for the match_type argument:

Match Type
Match Behaviour
Requirements
0 (default)
Exact match only (also allows wildcard matches)
1
Exact match or next smallest value
The lookup_array needs to be sorted in asc order
-1
Exact match or next largest value
The lookup_array needs to be sorted in desc order

.Lets take a closer look at the possible match types.

Exact match

You can perform an exact match search by supplying 0 for the match_type argument like this:

= MATCH(D5, B5:B14, 0)    // returns row index 6

Excel MATCH function - Exact Match

Note: MATCH is not case-sensitive, so “Joe” and “joe” will both return 6. 

Approximate match

To perform an approximate match search, you have to either set match_type to 1 (exact match or next smaller item) or -1 (exact match or next smaller item). In this context, you have to ensure that approximate matching works correctly by sorting your data in either ascending or descending order.

In the example shown below, the formula is:

= MATCH(D5, B5:B14, 1)    // returns row index of next smaller item

Excel MATCH function - Approximate Match

Wildcard match

If match_type is set to 0, MATCH also allows you to perform a match using wildcards (*?). In the example shown below, the formula is:

= MATCH(D5, B5:B14, 0)  // returns row index best wildcard match

Excel MATCH function - Wildcard Match

INDEX and MATCH

The Excel MATCH function is commonly used together with the INDEX function. In this combination, MATCH covers the task of locating and feeding a position to the INDEX function, which then returns the value at that given position. The basic syntax of INDEX MATCH looks like this:

= INDEX(lookup_array,MATCH(lookup_value,lookup_column, 0), col_ind))

In the above statement, MATCH locates the position of a lookup_value in the lookup_column, which is then used as the row_num index in the INDEX function. With a given lookup_array and col_ind for the result column, INDEX is then able to perform a flexible lookup that is even superior to the VLOOKUP function. 

One major reason why INDEX MATCH is often preferred over VLOOKUP function is the fact that you can perform lookup to the left and right, while VLOOKUP is limited to only look up to the right.

Additional Notes

If no match is found, MATCH throws an #N/A error. You can trap this error by wrapping MATCH with the IFNA function.

Formula examples

Related Tutorials

Related Functions

How to use the Excel VLOOKUP function

Excel VLOOKUP function

The Excel VLOOKUP function searches for a value by matching on the first table column and returns the corresponding value from a column in the same row.

How to use the Excel XMATCH function

Excel XMATCH function

The Excel XMATCH function searches for a specific item in a given range and returns the position. XMATCH is a more flexible and robust version of MATCH.

How to use the Excel INDEX function

Excel INDEX function

The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes.

Share on pinterest
Pinterest
Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on reddit
Reddit
Share on whatsapp
WhatsApp
Share on email
Email
Share on pinterest
Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on whatsapp
Share on email