How to Use MATCH in Excel: Syntax, Examples, and Tips

The MATCH function in Excel finds the position of a value in a range or array. You use it by specifying the value to find, the range to search, and the match type (exact or approximate). It returns the relative position of the value within the range.

Syntax

The MATCH function has three parts:

• lookup_value: The value you want to find.
• lookup_array: The range or array where you search.
• match_type: Optional. Use 0 for exact match, 1 for less than or equal (default), and -1 for greater than or equal.

excel

MATCH(lookup_value, lookup_array, [match_type])

Example

This example finds the position of the number 50 in the range A1:A5.

If A1:A5 contains: 10, 20, 50, 70, 90, the formula =MATCH(50, A1:A5, 0) returns 3 because 50 is the third item.

excel

=MATCH(50, A1:A5, 0)

Output

3

Common Pitfalls

• Using match_type incorrectly can cause unexpected results. For exact matches, always use 0.
• If the value is not found, MATCH returns #N/A.
• When using approximate matches (1 or -1), the lookup_array must be sorted ascending or descending accordingly.

excel

=MATCH(50, A1:A5)  <em>(may give wrong result if data not sorted)</em>

=MATCH(50, A1:A5, 0)  <em>(correct for exact match)</em>

Quick Reference

Parameter	Description	Example Values
lookup_value	Value to find	50, "Apple", B2
lookup_array	Range or array to search	A1:A10, {10,20,30}
match_type	Type of match	0 (exact), 1 (less or equal), -1 (greater or equal)

Key Takeaways

Use MATCH to find the position of a value in a range or array.

Always use 0 as match_type for exact matches to avoid errors.

MATCH returns #N/A if the value is not found.

For approximate matches, ensure the lookup range is sorted correctly.

MATCH works well combined with INDEX for powerful lookups.