Function MATCH – Dynamic VLOOKUP

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.

Related posts


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


1 comment

    • Sein Hla Maung on 17/06/2018 at 05:02
    • Reply

    Hi,

    Thank for your exercise file.

    Best Regards

    Sein Hla Maung

Leave a Reply

Your email address will not be published.