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 works generally. In this article, we will learn about the dynamic VLOOKUP to make our dataset easy and dynamic.
Download the following workbook and exercise.
3 Easy Ways to Use Dynamic 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. VLOOKUP function returns only a single matched value. But VLOOKUP with 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.
- First, make a drop-down list at Cell C11 by using the Data Validation option.
- Next, write down the ID in Cell B12 and select Cell C12.
- Type the following formula:
➥ Formula Breakdown
This will look up the exact match of value (C11) in Table 1 Headers. Make the row number absolute.
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.
- Hit Enter to see the result.
- Now we can change the header name from the drop-down to see another information.
- For that, Click on the drop-down menu beside the cell.
- After that, select the option and hit Enter to see the result.
Read More: VLOOKUP with Drop Down List in Excel
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.
- At first, write down the lookup ID.
- Select Cell C13.
- Now type the formula:
➥ Formula Breakdown:
This will help to get the column number.
This will return the exact match of Cell B13 and return the information from the array (Table2). Make sure to make the column & row numbers absolute.
- Then press Enter.
- After that, drag the Fill Handle icon to the right till Cell E13 and see the result.
- How to Add Multiple Cells in Excel (6 Methods)
- Extract Text Before Character in Excel (4 Quick Ways)
- How to Count Rows with Value in Excel (8 Ways)
- Remove First Character from String in Excel (6 Quick Ways)
- How to Combine Rows with Same ID in Excel (3 Quick Methods)
3. Using VLOOKUP with Excel COLUMNS Function
Sometimes we need the combination of the VLOOKUP function & COLUMNS function in the case of a large amount of data. COLUMNS function works differently from the COLUMN function. It returns the number of columns in a dataset. Let’s say we have an employee dataset. We are going to find out their information according to their name.
- Type a name in B13 from the list of names given in the Name column of the primary data table.
- Next select Cell C13.
- Write down the formula:
➥ Formula Breakdown:
This will count the number of columns in the range (B4:C4). Here make the first column absolute.
This will return the exact match of Cell B13 and return the value from the array (B4:D9). Make sure to make the column & row numbers absolute.
- Hit Enter.
- At last, drag Fill Handle rightward to see the required results at once.
These are the easiest ways to use dynamic VLOOKUP in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods in the comment section.