# How to use the MATCH function in Excel?

#### Frédéric LE GUEN

Last Updated on 02/09/2023
Reading time: 2 minutes

The MATCH function in Excel returns the position of a value in a range of cells. This function is very useful for creating dynamic research.
The MATCH function needs 3 arguments

1. The lookup value

It's the value you want to find in a range of cells

2. Range of the cells where is the value

It's necessarily, 1 column or 1 row

3. [Optional] The mode of research

Exact or Approximate (default value)

=MATCH(Lookup Value,Range of cells,mode of research)

## Result returns by MATCH

In the following example, we have the list of the days in a week. In E1, we select one day of the dropdown, and in E2, we write the following formula

=MATCH(E1,\$C\$1:\$C\$7,0)

The value returned is the position in the list of column C

• 1 for Monday
• 2 for Tuesday
• 3 for Wednesday
• ...

## Let's change the order of the days

Now, the formula is the same, but we have changed the order of the name

Now we have

• 1 for Thursday
• 2 for Monday
• 3 for Sunday

With these 2 examples, you can see that the order of your data is crucial for the result returned by the MATCH function.

## Explanation of the last parameter

In this example, the last parameter has not been indicated. In this situation, the formula returns a wrong result

Sunday is in the third position on the list and not 5.

So to be sure that your function MATCH returns the exact position in your list, it is compulsory to add the value 0 (option exact match).

## Result if the value isn't found.

If the value you search for is not in the list, the function returns #N/A

## MATCH function in action

Return the position of an item in a list isn't interesting. But if you associate MATCH with other functions, you can create GREAT dynamic dashboard.

### Associate with INDEX

When you build a formula with INDEX, the best way to return a value from your reference table is to use the MATCH function.

### Dynamic sum

In this example, the sum is recalculated according to the selected date. To do this, we have used a combination of SUM, INDEX and MATCH functions in Excel.

### Dynamic VLOOKUP

When you search for a value with the VLOOKUP function, the third argument (the column to be returned) is often directly written.

But with the MATCH function, you can create a dynamic document to return values from different columns.

# How to use the MATCH function in Excel?

Reading time: 2 minutes
Last Updated on 02/09/2023

The MATCH function in Excel returns the position of a value in a range of cells. This function is very useful for creating dynamic research.
The MATCH function needs 3 arguments

1. The lookup value

It's the value you want to find in a range of cells

2. Range of the cells where is the value

It's necessarily, 1 column or 1 row

3. [Optional] The mode of research

Exact or Approximate (default value)

=MATCH(Lookup Value,Range of cells,mode of research)

## Result returns by MATCH

In the following example, we have the list of the days in a week. In E1, we select one day of the dropdown, and in E2, we write the following formula

=MATCH(E1,\$C\$1:\$C\$7,0)

The value returned is the position in the list of column C

• 1 for Monday
• 2 for Tuesday
• 3 for Wednesday
• ...

## Let's change the order of the days

Now, the formula is the same, but we have changed the order of the name

Now we have

• 1 for Thursday
• 2 for Monday
• 3 for Sunday

With these 2 examples, you can see that the order of your data is crucial for the result returned by the MATCH function.

## Explanation of the last parameter

In this example, the last parameter has not been indicated. In this situation, the formula returns a wrong result

Sunday is in the third position on the list and not 5.

So to be sure that your function MATCH returns the exact position in your list, it is compulsory to add the value 0 (option exact match).

## Result if the value isn't found.

If the value you search for is not in the list, the function returns #N/A

## MATCH function in action

Return the position of an item in a list isn't interesting. But if you associate MATCH with other functions, you can create GREAT dynamic dashboard.

### Associate with INDEX

When you build a formula with INDEX, the best way to return a value from your reference table is to use the MATCH function.

### Dynamic sum

In this example, the sum is recalculated according to the selected date. To do this, we have used a combination of SUM, INDEX and MATCH functions in Excel.

### Dynamic VLOOKUP

When you search for a value with the VLOOKUP function, the third argument (the column to be returned) is often directly written.

But with the MATCH function, you can create a dynamic document to return values from different columns.