Excel MATCH function

Summary

The MATCH function in Excel is used to search for a specified value in a range and return the relative position of that item. It’s a fundamental tool for lookups, especially when combined with INDEX for more complex, dynamic searches. MATCH can perform exact matches, approximate matches, and wildcard matches.
Syntax
				
					=MATCH(lookup_value, lookup_array, [match_type])
				
			
  • lookup_value: The value that you want to search for.
  • lookup_array: The range of cells that contains possible matches.
  • [match_type]: [Optional] The type of match to perform: 1 for less than, 0 for exact match, 1 for greater than.
Return value
Returns the relative position of the found item in the lookup_array.

How to use

Choose the lookup_value you want to search for, specify the lookup_array where the search should take place, and define the match_type based on your requirement.

Match Types:

  • 1 or omitted: Finds the largest value that is less than or equal to lookup_value. lookup_array should be in ascending order.
  • 0: Exact match. If there are multiple values, the first is returned. lookup_array can be in any order.
  • 1: Finds the smallest value that is greater than or equal to lookup_value. lookup_array should be in descending order.

Examples

MATCH for Exact Match
Finding a Specific Item’s Position: To locate the exact position of “Apple” in a list:
				
					=MATCH("Apple", A2:A10, 0)
				
			
This formula searches for “Apple” in the range A2:A10 and returns its relative position.
MATCH for Approximate Match
Locating a Value in a Sorted Range: To find the position of the nearest smaller value to 100 in a sorted list:
				
					=MATCH(100, B2:B20, 1)
				
			
Assuming B2:B20 is sorted in ascending order, this formula finds the largest value less than or equal to 100 and returns its position.
MATCH for Partial Text Match
Using Wildcards to Match Partial Text: To search for a value that starts with “Pa” in a list:
				
					=MATCH("Pa*", C2:C15, 0)
				
			

This formula uses a wildcard (*) to find the first occurrence of a value beginning with “Pa” in the range C2:C15.

MATCH and INDEX
Dynamic Lookup with INDEX and MATCH: To return a specific data point based on dynamic criteria:
				
					=INDEX(D2:D15, MATCH("TargetItem", B2:B15, 0))
				
			
This combines MATCH with INDEX to find “TargetItem” in B2:B15 and returns the corresponding value from D2:D15.
MATCH for Reverse Lookup
Finding an Item in a Descending Order List: To locate a value in a list sorted in descending order:
				
					=MATCH(250, E2:E10, -1)
				
			
This searches for the smallest value greater than or equal to 250 in a descending-sorted range E2:E10.

Additional Notes

  • MATCH is case-insensitive and does not differentiate between uppercase and lowercase letters.
  • If no match is found, MATCH returns the #N/A error.
  • MATCH is often used in conjunction with INDEX for more complex and powerful lookup formulas.

Related Functions

Excel FILTER function

The Excel FILTER function dynamically isolates data in an array based on criteria, essential for targeted analysis and reporting.

Excel VLOOKUP function

The Excel VLOOKUP function searches a table's first column for a value, returning a specified column's value from the same row.

Excel INDEX function

The Excel INDEX function retrieves values at a specific row and column in a range, key for dynamic data lookups and retrieval.

Excel XLOOKUP function

The Excel XLOOKUP function searches for a value, offering flexible matching options and improvements over VLOOKUP and HLOOKUP.

Excel XMATCH function

The Excel XMATCH function returns the position of a value within a range, offering advanced matching and search capabilities.

Content Navigation