How to Use a Dynamic VLOOKUP in Excel – 3 Methods

Method 1 – Using a Dynamic 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:

Dynamic VLOOKUP with MATCH Function in Excel

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)

Dynamic VLOOKUP with MATCH Function in Excel

  • Press Enter to see the result.

Dynamic VLOOKUP with MATCH Function in Excel

  • 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

➤ MATCH(C$11,Table1[#Headers],0)

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

➤ VLOOKUP($B12,Table1,MATCH(C$11,Table1[#Headers],0),0)

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

Read More: 7 Practical Examples of VLOOKUP Function in Excel


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

VLOOKUP with Dynamic Column Reference

STEPS:

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

VLOOKUP with Dynamic Column Reference

  • 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

Using VLOOKUP with COLUMNS Function in Excel

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: How to Make VLOOKUP Case Sensitive in Excel


Download Practice Workbook

Download the following workbook and exercise.

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo