# How to Use a Dynamic VLOOKUP in Excel – 3 Methods

### Method 1 – Using aDynamic VLOOKUP with the MATCH Function

This is the sample dataset.

To find an employee’s information according to the ID and display it in C12:

STEPS:

• Create a drop-down list in C11 using Data Validation.
• Enter the ID in B12 and select C12.
• Enter the following formula:
`=VLOOKUP(\$B12,Table1,MATCH(C\$11,Table1[#Headers],0),0)`

• Press Enter to see the result.

• Change the header name in the drop-down list to see other information.

• Select the option and press Enter to see the result.

Formula Breakdown

looks up the exact match of value (C11) in Table 1 Headers. Makes the row number absolute.

returns the exact match from the whole dataset in B12 . The column number is absolute.

### Method 2 – Using the VLOOKUP with a Dynamic Column Reference in Excel

STEPS:

• Enter the lookup ID.
• Select C13.
• Enter the formula:
`=VLOOKUP(\$B\$13,Table2,COLUMN(B1),FALSE)`

• Press Enter.

• Drag the Fill Handle icon to the right till E13 and see the result.

Formula Breakdown

➤ COLUMN(B1)

helps to get the column number.

➤ VLOOKUP(\$B\$13,Table2,COLUMN(B1),FALSE)

returns the exact match of B13 from the array (Table 2). Makes the column & row numbers absolute.

### Method 3 – Using the VLOOKUP with the Excel COLUMNS Function

STEPS:

• Enter a name from the list in the Name column of the primary data table in B13 .
• Select C13.
• Enter the formula:
`=VLOOKUP(\$B13,\$B\$4:\$D\$9,COLUMNS(\$B4:C4),0)`

• Press Enter.
• Drag the Fill Handle to the right to see the result.

Formula Breakdown

➤ COLUMNS(\$B4:C4)

counts the number of columns in B4:C4. Makes the first column absolute.

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

returns the exact match in B13 from the array B4:D9. Makes the column and row numbers absolute.

Read More: 10 Best Practices with VLOOKUP in Excel

## Related Articles

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF