Quick Navigation

MATCH Function

Excel 2007+

Summary

The MATCH function searches for a specified value within a range of cells and returns its relative position. Perfect for dynamic lookups when paired with INDEX, MATCH excels at finding positions rather than values themselves.

Syntax

MATCH(lookup_value, lookup_array, [match_type])

Parameters

Parameter	Type	Required	Description
lookup_value	`Any`	Yes	Value to find in the lookup_array (number, text, logical value, or cell reference)
lookup_array	`Range`	Yes	Range of cells to search through
match_type	`Number`	No	Match behavior: 1 (or omitted) for largest ≤ value (ascending), 0 for exact match, -1 for smallest ≥ value (descending)

Using the MATCH Function

MATCH is the positioning powerhouse of Excel lookups. While VLOOKUP returns values, MATCH returns positions—making it the perfect companion for INDEX function to create flexible, two-way lookups that outperform traditional VLOOKUP/HLOOKUP.

Common MATCH Examples

Exact Match Position

=MATCH(25,A1:A3,0)

Returns 2 because 25 is the second item in range A1:A3 containing {5,25,38}.

Approximate Match (Ascending)

=MATCH(39,B2:B5,1)

Returns 2 (position of 38) since no exact match and data is sorted ascending.

Wildcard Text Match

=MATCH("*ana",A1:A10,0)

Finds first cell containing 'ana' anywhere in the text using asterisk wildcard.

INDEX + MATCH Combo

=INDEX(B1:B10,MATCH(D1,A1:A10,0))

Dynamic lookup: finds position with MATCH, retrieves value with INDEX.

Frequently Asked Questions

MATCH returns #N/A error. Wrap with IFERROR to handle gracefully.

No, MATCH is case-insensitive for text comparisons.

XMATCH offers more search modes, works backwards by default, and is available only in Excel 365/2021.

Yes, but only for exact matches (match_type=0). Approximate matches require sorted data.

Common Errors and Solutions

#N/A - No Match Found

Cause: lookup_value doesn't exist in lookup_array

Solution: Verify data exists or use IFERROR to handle

#N/A - Unsorted Data

Cause: Using match_type 1 or -1 with unsorted range

Solution: Sort data ascending (1) or descending (-1)

Wrong Position Returned

Cause: lookup_array includes headers or wrong range

Solution: Ensure lookup_array contains only searchable data

Notes

Default match_type is 1 (approximate match, ascending order)
Use array constants: MATCH("b",{"a","b","c"},0) returns 2
Wildcards (? for single char, * for multiple) work only with match_type=0
Escape wildcards with tilde (~?) or (~*)
Often combined with INDEX for VLOOKUP alternative

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+