# MATCH Function

Get the position of a value within an array

## What is the MATCH Function?

The MATCH function is categorized under 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) – It is the value that we want to look up.
2. Lookup_array (required argument) – It is 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 MATCH function, let’s consider a few examples:

#### Example 1

Suppose we are given the following data:

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

We get the result below:

#### Example 2

Suppose we are given the following data:

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:

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

### Things to remember about the MATCH Function

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.

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional resources:

• Excel Functions for Finance