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
- 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
Step 2: Replace the third argument of VLOOKUP
Now, we replace the third argument of VLOOKUP with the MATCH function
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.