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.
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:
- Excel Tables
- Dynamic named ranges
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.
To set-up a Table, just highlight the cells and click Insert -> Table (Shortcut: Ctrl + T)
The Create Table window will open.
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).
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.
Using the Table
We can now use the “TelephoneList” Table as the table_array within the VLOOKUP.
Cell F4 includes the following formula:
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.
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.
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.
A new window will appear in which you can enter the information for your named range. Then click OK.
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.
Look at function used in this example.
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
Look at function used in this example.
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.
Cell F4 includes the following formula:
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.
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.
Download the file: Advanced VLOOKUP Cheat Sheet
Other posts in the Mastering VLOOKUP Series
- How to use VLOOKUP
- VLOOKUP: What does the True/False statement do?
- VLOOKUP: How to calculate faster
- How to VLOOKUP to the left
- VLOOKUP: Change the column number automatically
- VLOOKUP with multiple criteria
- Using wildcards with VLOOKUP
- How to use VLOOKUP with columns and rows
- Automatically expand the VLOOKUP data range
- VLOOKUP: Lookup the nth item (without helper columns)
- VLOOKUP: List all the matching items
- Advanced VLOOKUP Cheat Sheet
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: