Search

Dynamic VLOOKUP research

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

Related posts

Compare 2 Columns

Frédéric LE GUEN

Anonymise your data

Frédéric LE GUEN

Filter your data in Excel

Frédéric LE GUEN

1 comment

Sein Hla Maung 17/06/2018 at 05:02

Hi,

Thank for your exercise file.

Best Regards

Sein Hla Maung

Reply

Leave a Comment