How to build a Dynamic VLOOKUP with the MATCH function?

• The challenge with the VLOOKUP function is to specify the column number from which you want to return data.
• The idea is to replace this argument with the MATCH function
• Like that, we will have a Dynamic VLOOKUP function

The third argument of VLOOKUP returns the column

Let’s consider a scenario where you have created a VLOOKUP function to return the State name based on the Employee ID.

• We search for the ID in cell C14
• Our reference table is the Table tbl_Employee (A1:G12)
• We want to return the 6th column (the State Name column)
• Finally, 0 to perform an Exact Match

The result is correct. But if we want to return the employee’s name instead, we must manually change the third argument of the VLOOKUP function from 6 to 2.

This is why it’s more efficient to create a dynamic VLOOKUP, where the column index is linked to a dropdown list 😉

How to create a Dynamic VLOOKUP

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

• The MATCH function returns the position of an item in a list.
  • First argument: The text to search
  • Second argument: The array where to search the first argument
  • Third argument: The match mode (exact in this situation)

For instance, to return Wednesday’s position in the list of weekdays, we will write the following formula:

In our scenario, we will use the MATCH function to find the position of a title column in the headers.

Then, we replace the third argument inside the original VLOOKUP function with the MATCH function, and the job is done.

Add a dropdown list to select the column header.

The easiest way to select any column header is to build a dropdown list. Then you just have to select one of them to return the result for this column.