MATCH
Returns the position of a value in a range
=MATCH(lookup_value, lookup_array, [match_type])This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns the position of a value in a range
The MATCH function identifies the relative position of a specified value within a range of cells, rather than returning the value itself. It is a fundamental building block for advanced formulas, most notably when paired with INDEX to perform flexible vertical or horizontal lookups. The function supports three match types: 0 (exact match), 1 (less than, requiring ascending sort), and -1 (greater than, requiring descending sort). Best practices dictate explicitly setting the [ype] to 0 for most business use cases to avoid errors caused by unsorted data. MATCH is case-insensitive but supports wildcards (* for multiple characters, ? for a single character) when the ype is set to 0. For modern spreadsheets, users should note that XLOOKUP often replaces INDEX/MATCH combinations as it includes a built-in [ound] argument and handles array directions more intuitively.
Quick reference
Syntax
=MATCH(lookup_value, lookup_array, [match_type])Inputs
Arguments
Example: A2
Example: A:A
Example: 0
Formula patterns
Examples
Exact match for a product ID
=MATCH(D2, A2:A50, 0)Finding a position in a horizontal header row
=MATCH(E1, A1:Z1, 0)Approximate match for tax brackets or commissions
=MATCH(F2, B2:B10, 1)Avoid these issues
Common Errors
#N/A
Cause: The lookup_value is not found within the lookup_array, or the array is not sorted correctly for match_type 1 or -1.
Fix: Verify the search value exists and check your sort order. Consider using XLOOKUP which has a built-in [if_not_found] argument to handle these cases gracefully.
#VALUE!
Cause: The lookup_array argument is not a one-dimensional range (it spans multiple rows and multiple columns).
Fix: Ensure the lookup_array is a single column or a single row.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns the position of a value in a range
alue: The value to find rray: The range to search ype: 0=exact, 1=less than or equal, -1=greater than or equal
#N/A: Verify the search value exists and check your sort order. Consider using XLOOKUP which has a built-in [ound] argument to handle these cases gracefully. #VALUE!: Ensure the rray is a single column or a single row.