Dynamic VLOOKUP research

Dynamic VLOOKUP research
Last Updated on 30/06/2020
Reading time: 2 minutes

VLOOKUP returns only 1 column

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

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 😤😡💥)

Create a dynamic VLOOKUP

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

Step 1: Find the position of the header

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

Step 2: Replace the third argument of VLOOKUP

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

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

Step 3: Change the header name

Now, when you change the name of the header column, the MATCH function will return the value of the column (here between 1 and 5).

Like this, automatically, the VLOOKUP function will return the value of the column selected.

VLOOKUP returns value from variable columns

1 Comment

  1. Sein Hla Maung
    17/06/2018 @ 05:02

    Hi,

    Thank for your exercise file.

    Best Regards

    Sein Hla Maung

    Reply

Leave a Reply

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

Dynamic VLOOKUP research

Reading time: 2 minutes
Last Updated on 30/06/2020

VLOOKUP returns only 1 column

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

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 😤😡💥)

Create a dynamic VLOOKUP

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

Step 1: Find the position of the header

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

Step 2: Replace the third argument of VLOOKUP

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

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

Step 3: Change the header name

Now, when you change the name of the header column, the MATCH function will return the value of the column (here between 1 and 5).

Like this, automatically, the VLOOKUP function will return the value of the column selected.

VLOOKUP returns value from variable columns

1 Comment

  1. Sein Hla Maung
    17/06/2018 @ 05:02

    Hi,

    Thank for your exercise file.

    Best Regards

    Sein Hla Maung

    Reply

Leave a Reply

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