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.