MATCH Function

Get the position of a value within an array

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

What is the MATCH Function?

The MATCH Function[1] is categorized under Excel Lookup and Reference functions. It looks up a value in an array and returns the position of the value within the array. For example, if we wish to match the value 5 in the range A1:A4, which contains values 1,5,3,8, the function will return 2, as 5 is the second item in the range.

In financial analysis, we can use the MATCH function along with other functions to look up and return the sum of values in a column. It is commonly used with the INDEX function. Learn how to combine INDEX MATCH as a powerful lookup combination.

Formula

=MATCH(lookup_value, lookup_array, [match_type])

The MATCH formula uses the following arguments:

  1. Lookup_value (required argument) – This is the value that we want to look up.
  2. Lookup_array (required argument) – The data array that is to be searched.
  3. Match_type (optional argument) – It can be set to 1, 0, or -1 to return results as given below:

 

Match_typeBehavior
1 or omittedWhen the function cannot find an exact match, it will return the position of the closest match below the lookup_value. (If this option is used, the lookup_array must be in ascending order).
0When the function cannot find an exact match, it will return an error. (If this option is used, the lookup_array does not need to be ordered).
-1When the function cannot find an exact match, it will return the position of the closest match above the lookup_value. (If this option is used, the lookup_array must be in descending order).

How to Use the MATCH Function in Excel

To understand the uses of the function, let’s consider a few examples:

Example 1

Suppose we are given the following data:

MATCH Function

We can use the following formula to find the value for Cucumber.

MATCH Function - Example 1

We get the result below:

MATCH Function - Example 1a

Example 2

Suppose we are given the following data:

MATCH Function - Example 2

Suppose we wish to find out the number of Trousers of a specific color. Here, we will use the INDEX or MATCH functions. The array formula to use is:

MATCH Function - Example 2a

We need to create an array using CTRL + SHIFT + ENTER. We get the result below:

MATCH Function - Example 2b

Things to Remember

  1. The MATCH function does not distinguish between uppercase and lowercase letters when matching text values.
  2. N/A! error – Occurs if the match function fails to find a match for the lookup_value.
  3. The function supports approximate and exact matching and wildcards (* or ?) for partial matches.

Click here to download the sample Excel file

Additional Resources

Thank you for reading CFI’s guide to the MATCH Function. To learn more, check out these additional CFI resources:

Article Sources

  1. MATCH Function
0 search results for ‘