The MATCH formula with example in Excel

The MATCH function in Excel is used to search for a specified item in a range of cells and return the relative position of that item within the range. It's useful for finding the position of an item in a list.

Syntax

MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: The value you want to search for.
• lookup_array: The range of cells containing the value.
• [match_type] (optional): The type of match:
  • 1 or omitted: Finds the largest value less than or equal to lookup_value (requires the data to be sorted in ascending order).
  • 0: Finds the exact match.
  • -1: Finds the smallest value greater than or equal to lookup_value (requires the data to be sorted in descending order).

Example

Suppose you have a list of names and you want to find the position of a specific name in the list.

Data

A
Alice
Bob
Charlie
David
Eve

You want to find the position of "Charlie" in this list.

Steps

1. Enter the names in column A (A1).

2. Use the MATCH function to find the position of "Charlie". In cell B1, enter the following formula:

   =MATCH("Charlie", A1:A5, 0)
   

3. Press Enter.

Result

The formula returns 3, indicating that "Charlie" is the third item in the list.

Explanation of the Formula

• "Charlie" is the lookup_value you are searching for.
• A1:A5 is the lookup_array containing the list of names.
• 0 specifies that you want an exact match.

Another Example with Numerical Data

Suppose you have a list of numbers, and you want to find the position of the number 85.

Data

A
10
25
50
85
100

You want to find the position of 85 in this list.

Steps

1. Enter the numbers in column A (A1).

2. Use the MATCH function to find the position of 85. In cell B1, enter the following formula:

   =MATCH(85, A1:A5, 0)
   

3. Press Enter.

Result

The formula returns 4, indicating that 85 is the fourth item in the list.

By using the MATCH function, you can easily locate the position of an item within a range, which is particularly useful when combined with other functions like INDEX for more advanced lookup operations.