Dynamically Reference Excel Tables Inside A Vlookup Function

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.

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

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.

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”.

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)

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.

Format Excel Charts Like a Pro!!!

Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!

Get These Hacks Now For Free!

Keep Learning

Explore more →

Which Excel Versions Have XLOOKUP Function? [Solved]

Versions Of Microsoft Excel For XLOOKUP Function The following versions of Microsoft Excel include the XLOOKUP function: Excel 2021 or...

4 PRO Ways to Create A Filtering Search Box [Excel Download]

Creating A Search Box In Excel It's unfortunate Excel does not have a form control search box (maybe in the...

Build A United States Dashboard Map Chart In Excel

Map Charts In Excel Are Limited… Let’s face it, Excel’s built-in Map Chart feature seemingly was thrown together overnight. Its...

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.