«

»

Function MATCH

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.

Presentation of the MATCH function

MATCH function only needs two mandatory parameters and an optional parameter

  • The researched value
  • Reference data (a range of cell) with necessarily 1 dimension (a single row or a single column)
  • [Optional] Exact value or an approaching value (possible values 0, 1 or -1 and 1 is the default value)

In fact, the writing of MATCH is like the VLOOKUP function except for the parameter to return the column value.

Basic example with MATCH

In the following document, you have in column A the list of days in a week. And we want to return the position of a day in C2.

In C1, we have a created a list with the data validation tool. The source is simply the column A.

Make a test !!!

Change the value of the day. Automatically, the value in C2 change to return the position of the day in the list of days.

You can see the formula by double-clicking in the cell C2.

=MATCH(C1,A1:A7,0)

If there is no match, the function returns #N/A

The 0 is needed in this example because days do not follow the alphabetical order. So to make sure the function returns the position of a value in your list, add the optional value 0 or FALSE

Interest of MATCH with VLOOKUP

MATCH function is not really useful for itself. But, you can associate it with VLOOKUP and INDEX functions to build the research parameters of these functions.

Consider the following document with a lot of columns header
In another document, you want to collect only few column

To return the value of the country, you can write this VLOOKUP function

=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

Ok, the function works but if we copy the formula for the other columns, we must change the value of the column to return (the third parameter) manually (such a waste of time 😤😡💥)

So the idea is to replace the third parameter by a MATCH function. And this function is based on the value of the header

So if we want to know the position of the word "Country" in the header of the reference table, we will write this function

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

So, the function MATCH return 4. That's exactly what we want to find for the third parameter of the VLOOKUP function. 😍😍😍

So, we replace the third parameter 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.

 

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/function-match/


Leave a Reply

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