# How to Use Dynamic VLOOKUP in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Dynamic VLOOKUP is used to find the value easily from a large amount of data in Excel. VLOOKUP means Vertical Lookup. As regular users of Excel, we all know how the VLOOKUP function generally works. In this article, we will learn about the dynamic VLOOKUP to make our dataset easy and dynamic.

## How to Use Dynamic VLOOKUP in Excel: 3 Easy Ways

Here, we will demonstrate 3 easy ways to use dynamic VLOOKUP in Excel. This section provides extensive details on these methods. Therefore, you should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.

Read More: 10 Best Practices with VLOOKUP in Excel

### 1. Excel Dynamic VLOOKUP with MATCH Function

The MATCH function finds an exact or appropriate match of data and returns the position in a given range. The VLOOKUP function returns only a single matched value. But VLOOKUP with the MATCH function makes the formula dynamic. Itâ€™s a two-way dynamic lookup. Assuming we have Table 1 of employeesâ€™ names with their ID, AGE & SALARY. We are going to find the employeeâ€™s single information according to their ID at Cell C12. Therefore, follow the below steps.

STEPS:

• Firstly, make a drop-down list in cell C11 by using the Data Validation option.
• Next, write down the ID in cell B12 and select cell C12.
• Then, type the following formula:
`=VLOOKUP(\$B12,Table1,MATCH(C\$11,Table1[#Headers],0),0)`

• After that, hit Enter to see the result.

• Subsequently, we can change the header name from the drop-down to see other information.
• For that, Click on the drop-down menu beside the cell.

• Lastly, select the option and hit Enter to see the result.

Formula Breakdown

Firstly, this will look up the exact match of value (C11) in Table 1 Headers. Make the row number absolute.

At last, this will return the exact match of the required cell, cell B12â€™s information from the whole dataset. Here the column number will be absolute.

### 2. VLOOKUP with Dynamic Column Reference in Excel

To get a column reference, we can use the COLUMN function. This function saves a lot of time. It makes the column into a cell reference in an array. Here we have an employee dataset. We are going to find the total information of an employee with his/her ID by using a dynamic column reference. So, learn the below steps.

STEPS:

• First, write down the lookup ID.
• Now, select cell C13.
• Next, type the formula:
`=VLOOKUP(\$B\$13,Table2,COLUMN(B1),FALSE)`

• Then, press Enter.

• After that, drag the Fill Handle icon to the right till cell E13 and see the result.

Formula Breakdown

âž¤ COLUMN(B1)

At first, this will help to get the column number.

âž¤ VLOOKUP(\$B\$13,Table2,COLUMN(B1),FALSE)

Lastly, this will return the exact match of Cell B13 and return the information from the array (Table 2). Make sure to make the column & row numbers absolute.

### 3. Using VLOOKUP with Excel COLUMNS Function

Sometimes we need the combination of the VLOOKUP function and the COLUMNS function in the case of a large amount of data. The COLUMNS function works differently from the COLUMN function where it returns the number of columns in a dataset. Letâ€™s say we have an employee dataset. So, we are going to find out their information according to their name. Therefore, learn the following process.

STEPS:

• First, type a name in B13 from the list of names given in the Name column of the primary data table.
• Next, select cell C13.
• Then, write down the formula:
`=VLOOKUP(\$B13,\$B\$4:\$D\$9,COLUMNS(\$B4:C4),0)`

• Afterward, hit Enter.
• Finally, drag the Fill Handle rightward and see the required results at once.

Formula Breakdown

âž¤ COLUMNS(\$B4:C4)

Firstly, this will count the number of columns in the range (B4:C4). Here make the first column absolute.

âž¤ VLOOKUP(\$B13,\$B\$4:\$D\$9,COLUMNS(\$B4:C4),0)

At last, this will return the exact match of cell B13 and return the value from the array (B4:D9). Again, make sure to make the column and row numbers absolute.

## Conclusion

These are the easiest ways to use dynamic VLOOKUP in Excel. Afterward, there is a practice workbook added. So, go ahead and give it a try. Lastly, in the comment section, feel free to ask anything or suggest any new methods.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF