**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
(A1:G12)**tbl_Employee** - 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.

Sein Hla Maung

17/06/2018 @ 05:02

Hi,

Thank for your exercise file.

Best Regards

Sein Hla Maung