×

Dynamically Reference Excel Tables Inside A Vlookup Function

By Chris Newman •  Updated: 02/03/21 •  4 min read
How to Dynamically Reference Excel Table in Vlookup

In this article, we are going to explore how to reference a specific Excel Table object from a drop-down list inside a VLOOKUP formula. In the below GIF, you can see the user is selecting a Revenue Type from a drop-down list and then can proceed to look up a corresponding name from that particular table to yield a sales amount.

Excel example of using VLOOKUP function to dynamically reference excel tables

If you would like to download the example file used within this article please click the below button to download the example Microsoft Excel File.

Setting Up Your Spreadsheet

Adding Table Objects

In this example, 3 Excel tables have been created (shortcut Ctrl + t). I have also named the table objects “Affiliate”, “Consulting”, and “Product”. I did this by:

  1. Selecting a cell in the table
  2. Navigating to the Table Design tab in Excel’s Ribbon
  3. Typing the desired text (no spaces allowed) in the Table Name text box

    • Your table name can be anything you want, it does not have to be one of the header names
  4. Hitting the ENTER key on my keyboard to submit the change
Rename Excel Table Object

Creating The Drop-Down List

Next, you will need to create a drop-down menu utilizing Data Validation to ensure your users select your table names. You can do this easily by selecting the cell you want the list to be stored in and:

  1. Navigate to the Data tab in your Excel Ribbon
  2. Click the Data Validation button in the Data Tools button group
  3. In the Settings tab of the Data Validation dialog box, set the Allow field to “List
  4. In the Source field, type out each one of your table names using a comma to separate the names
  5. Click OK

You should now see a drop-down menu button whenever you select the cell you added the Data Validation rule to.

Excel Data Validation source drop-down menu in Excel

How to Write the VLOOKUP Formula

Now that you have your Table names defined and a drop-down list with the Table names that your user can select from, the last piece to this solution is writing a VLOOKUP formula that can dynamically point to different tables based on the user’s selection. For this to occur you will need to embed the INDIRECT function into your VLOOKUP function.

The INDIRECT function outputs a range reference based on the text that you give it. For example, if I write: =INDIRECT(“Affiliate”) that formula would literally return the range reference of the table —>“B3:C5”.

Excel Indirect Function Example

We can utilize the functionality of the INDIRECT function to control the Table_Array input of our VLOOKUP function. If you were only looking up a single table, you would write the VLOOKUP formula as follows:

=VLOOKUP(D4,Affiliate,2,0)

To make the table name reference dynamic, you will need to replace the static “Affiliate” table name with the INDIRECT function:

=VLOOKUP(D4,INDIRECT(D2),2,0)
Vlookup function to reference excel tables.

To polish up the formula a bit, I recommend adding an error handler in case the lookup value is not found in the table. To do this, wrap an IFERROR function around your entire formula.

=IFERROR(VLOOKUP(D4,INDIRECT(D2),2,0),”Not Found”)

I Hope This Helped!

Hopefully, I was able to explain how you can use the combination of INDIRECT and VLOOKUP to dynamically look up values from multiple Excel Table Objects within your spreadsheets. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.