Automatically expand the VLOOKUP data range

The 2nd argument in the VLOOKUP function is the table_array.  This is the range of cells which contains the data to be used within the VLOOKUP function.    For example, in the code below A1:C9 is the table_array.

=VLOOKUP("O Redding",A1:C9,3,FALSE)

When using the VLOOKUP function, we often know how many rows there are in the data, therefore we know which cell references to include in the table_array.  But, there are also circumstances when additional rows will be added onto the existing data, therefore the exact data ranage is not known.  If you have experienced this, you probably followed one of the following methods:

  • Included a table_array which is significantly bigger than required so that future data can be added (this method can result in slower calculation times).
  • Updated the table_array argument each time the data changes to include the additional data (this method requires regular updates/changes to the VLOOKUP formula).
  • Inserted the new rows into the existing table_array, rather than at the bottom (this method requires you to remember to insert them into the middle and not at the bottom.  Also, how will other users know to add the new data into the middle).

But, did you know there are other ways?  Ways, which once set-up, do not require the VLOOKUP function to be updated or the clever placement of the data.  In this post, we will consider these options:

  1. Excel Tables
  2. Dynamic named ranges

Excel Tables

An Excel Table is an often misunderstood feature, but we won’t go into full detail here.  The key point for this post is that a Table expands or retracts automatically to fit the data.

Table set-up

To set-up a Table, just highlight the cells and click Insert -> Table (Shortcut: Ctrl + T)

VLOOKUP Auto Expand Insert Table

The Create Table window will open.

VLOOKUP Auto Expand Create Table

Our data has headers and is within the range shown, so we can click OK.

The table has now formatted automatically to be striped (this can easily be changed if you so wish).

VLOOKUP Auto Expand New Table

We will rename the Table to a more useful name.  Click anywhere in the Table, then click Table Tools: Design in the Ribbon.  Rename the Table in the Table Name box.

VLOOKUP Auto Expand Table Rename

Using the Table

We can now use the “TelephoneList” Table as the table_array within the VLOOKUP.

VLOOKUP Auto Expand Table Function

Cell F4 includes the following formula:

=VLOOKUP(F2,TelephoneList,3,FALSE)

Each time new rows are added to the data, the source of the VLOOKUP will automatically be updated to include these cells.  In the screenshot below, B Adams has been added to Cells A10-C10 and “TelephoneList” has automatically updated too.  B Adams can now be found in the VLOOKUP withou needing to change the VLOOKUP formula.

VLOOKUP Auto Expand new data

Dynamic named ranges

A named range is a group of cells which can be referred to by another name.  For example, you may click on Cells A1-C9 and give them the name “myRange”.   “myRange” can now be used within formulas instead of referencing A1-C9.

To set-up a named range you would use one of two methods

Named range quick set-up

To quickly set-up a named range, just highlight the cells and type the name into the range box.

VLOOKUP named range quick

Defining the name

For a more detailed set-up, use the Define Name section of the Formulas Ribbon.

Using the menus click: Formulas -> Define Names -> Define Name.

VLOOKUP named range defined name

A new window will appear in which you can enter the information for your named range.  Then click OK.

VLOOKUP named range - new range

Setting up a dynamic named range

But we can go a step further.  We can use formulas to let Excel workout the size the named range should be, this is called a dynamic named range.  By using a dynamic named range, it will automatically expand to include any new data added to the bottom.

These dynamic named ranges experience issues when there are blank cells or where there is additional data inserted below the lookup table.  There are two different methods used below to tackle these two issues.

For either option setup the dynamic named range by defining the name.  But instead of using references to a range of cells, we will use the INDEX function to create the range.

OPTION 1: Base the range on the last used cell in a column.

VLOOKUP Auto Expand Range-Dynamic Named

Look at function used in this example.

=Sheet1!$A$1:INDEX(Sheet1!$C:$C,MAX((Sheet1!$C:$C<>"")*(ROW(Sheet1!$C:$C))))

This is an Array Formula, so press Ctrl + Shift + Enter to enter the formula into the Refers to: box.

The formula starts with the sheet name and a reference to the first cell.  The INDEX function is used in conjunction with the MAX, and ROW functions to return the address of the last used cell in the column.  In the example above the INDEX function is returning a cell reference from Column C.

This formula still works where there are blank cells, but may create ranges bigger than necessary if there are any used cells below the lookup_data.

OPTION 2: Count the number of cells in a column

VLOOKUP Auto Expand COUNTA - Dynamic Named

Look at function used in this example.

=Sheet1!$A$1:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$C:$C))

The formula starts with the sheet name and a reference to the first cell.  The INDEX function is then used in conjunction with the COUNTA functions to return the address based on the number of non-blank cells.  In the example above the INDEX function is returning a cell reference from Column C.

This formula does not work correctly where there are blank cells, as it may create a range smaller than necessary.

Using a dynamic named range with VLOOKUP

We can now use “myRange” as the table_array within the VLOOKUP.

VLOOKUP Dynamic Named Range in Formula

Cell F4 includes the following formula:

=VLOOKUP(F2,myRange,3,FALSE)

Each time new rows are added to the data, the VLOOKUP will automatically be updated to include these cells.  B Adams has been added to Cells A10-C10 and “myRange” has automatically updated too.  B Adams can now be found in the VLOOKUP.

VLOOKUP Dynamic Named Range New Line

You could keep adding data up to 500 rows and the VLOOKUP would work.  If you want more than 500 rows just update the cell references to the INDEX function within “myRange”.

Download the Advanced VLOOKUP Cheat Sheet

Download the Advanced VLOOKUP Cheat Sheet.  It includes most of the tips and tricks we’ve covered in this series, including faster calculations, multiple criteria, left lookup and much more.

Please download it and pin it up at work, you can even forward it onto your friends and co-workers.

Advanced VLOOKUP Cheat Sheet

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0166 Advanced VLOOKUP.pdf

Other posts in the Mastering VLOOKUP Series


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

3 thoughts on “Automatically expand the VLOOKUP data range”

  1. Interesting! I appreciate the Table explanation.

    For the dynamic named range…you still have to adjust the reference if you wanted to go beyond 500, correct?

    Why wouldn’t you just put the vlookup array to $A$1:$C$1000? If that were the case, what’s the benefit of the dynamic range?

    Reply
    • Hi David,

      To be honest, I tend to use the Tables method rather than the dynamic named range method, it’s just much easier.

      Dynamic named ranges of a completely unknown size have problems when used with blank cells or additional cells below the data table. I thought I had created a good solution, but you’re right, on second thoughts, there is little (if any) benefit from that approach. I have updated the article to show the two possible (but potentially flawed) dynamic named range approaches.

      Thank you for taking the time to point this issue out to me.

      Reply

Leave a Comment