Home » Function » Lookup functions » How to use the MATCH function in Excel

How to use the MATCH function in Excel

Reading time: 2 minutes
Last Updated on 14/08/2022 by Frédéric LE GUEN

What does the MATCH function?

The MATCH function 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 parameters.

  • The value to find
  • Range of the cells where is the value (1 row or 1 column)
  • [Optional] Exact value or an approaching value (possible values 0, 1 or -1 and 1 is the default value)

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)

MATCH returns the position of the days

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

MATCH returns the postion according the sort of the data

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 in the list and not 5.

The last argument is not fill and the MATCH function returns a wrong result

So to be sure that your function MATCH returns the accurate position of a value 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 is not in the list, the function returns #N/A

Situation where MATCH returns NA

MATCH in action

Return the position of an item in a list isn't really 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

For instance, in function of the date selected, the sum is recalculated to reflect the selection. Explanation in this article.

Dynamic SUM in Excel

Dynamic VLOOKUP

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

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

VLOOKUP returns value from variable columns

Leave a Reply

Your email address will not be published. Required fields are marked *

How to use the MATCH function in Excel

Reading time: 2 minutes
Last Updated on 14/08/2022 by Frédéric LE GUEN

What does the MATCH function?

The MATCH function 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 parameters.

  • The value to find
  • Range of the cells where is the value (1 row or 1 column)
  • [Optional] Exact value or an approaching value (possible values 0, 1 or -1 and 1 is the default value)

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)

MATCH returns the position of the days

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

MATCH returns the postion according the sort of the data

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 in the list and not 5.

The last argument is not fill and the MATCH function returns a wrong result

So to be sure that your function MATCH returns the accurate position of a value 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 is not in the list, the function returns #N/A

Situation where MATCH returns NA

MATCH in action

Return the position of an item in a list isn't really 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

For instance, in function of the date selected, the sum is recalculated to reflect the selection. Explanation in this article.

Dynamic SUM in Excel

Dynamic VLOOKUP

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

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

VLOOKUP returns value from variable columns

Leave a Reply

Your email address will not be published. Required fields are marked *