Presentation of the MATCH function
The MATCH function returns the position of a value in a range of cells
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
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
- 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
Now, we replace the third parameter of VLOOKUP with the MATCH function
Like this, you have create a single formula and you can copy it for all the other cells, whatever the column header.