Sommaire

## Presentation of the MATCH function

**The MATCH function returns the position of a value in a range of cells**

This function is very useful **to create dynamic research** with the function VLOOKUP or INDEX.

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**

It's like the function VLOOKUP except you don't have to return the column index

## Basic example with 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)

With this formula, the result is the position of the day in the list

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

As you see, the formula returns the position of the value selected in function of the order of the original list.

## Result if the value is not found

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

## Explanation of the last parameter

In this example, the last parameter has been not indicated. And has you can see, the formula returns a wrong result. Sunday is in the third position in the list and not 5.

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).

## Create a dynamic VLOOKUP

Consider the following document with a lot of columns.

You want to extract in another document only few columns. To do that, you will create function VLOOKUP like this one

=VLOOKUP($A8,$A$2:$E$5,4,0)

With

**A8**the reference of the fruit to research**$A$2:$E$5**the range of cells of our reference document**4**the column to return**0**Exact match

## Replace the third parameter by MATCH

Ok, the function works but **if we copy the formula for the other columns**, **we must change the column index manually** (such a waste of time 😤😡💥)

So the idea is to **replace the third parameter by a MATCH function**.

We are going to use the position of the values in the header and return this position in the parameter of VLOOKUP

To return the position of a specific header, we are going to write this formula

=MATCH("Country",$A$1:$E$1,0) =>4

Now, we replace the third parameter of VLOOKUP with the MATCH function

=VLOOKUP($A8,$A$2:$E$5,MATCH(B$7,$A$1:$E$1,0),0)

Like this, you have create a single formula and you can copy it for all the other cells, whatever the column header.

## 1 comment

Hi,

Thank for your exercise file.

Best Regards

Sein Hla Maung