I’m fresh back from the Excel Power Analyst Boot Camp we had here in Dallas. If you were there, thanks for all the great tips – I had lots of fun. If you weren’t there, you missed quite the event. The Spice Girls showed up to talk about their new movie: Spice Racks. It was awesome. Lesson learned eh? Next time you’ll join us.
One of the topics that kept surfacing at last week’s event was the use of Tables/Lists. These are auto-expanding ranges that allow anything built on top of them to keep up with your data changes (similar to the dynamic named ranges you can create by using OFFSET and COUNTA, but much simpler). Today, I’ll show you how to employ Tables/Lists to protect your VLOOKUPS from breaking when you add data to your lookup range.
The Problem:
VLOOKUPs are typically tied to a static table arrays (in this example, the table array is A6:B10). By static, I mean that the arrays to which VLOOKUPs are tied usually don’t auto-expand when data is added.
Case in point – Although I added a new row, the VLOOKUP doesn’t automatically recognize the range has changed. So it just fails.
The Solution:
The solution is to turn your table array into a 2007 ‘Table’ or a 2003 ‘List’. Again, Tables/Lists allow you to create a defined range that will automatically shrink or expand with the data. This enables any component, chart, pivottable or formula tied to that range to keep up with changes in your data.
In Excel 2007, highlight your range and click Table on the Insert tab.
In Excel 2003, highlight your range and choose Data ->List->Create List.
It’s the same functionality between the two versions, only different names. I’m not sure why Microsoft changed the name from List to Table. It may have something to do with SharePoint and the association of the word ‘list’ to SharePoint Lists.
Once your table array has been converted to a Table/List, then you can rebuild your VLOOKUP formula to point to the table/list. Notice in the formula shown below, the table array argument now points to Table2. This is a Excel 2007 thing (Excel created an internal named range). In Excel 2003, pointing your formula to a List won’t show you a named range – it shows you a normal range.
Now, my VLOOKUP keeps up with any new rows I add.
Obviously, this technique is really only valuable where you have VLOOKUPS that are searching highly volatile table arrays. For example, I maintain dashboard reports that contain VLOOKUPS tied to ranges that change in size from one reporting month to another. Instead of constantly re-jiggering my VLOOKUP formulas, I’ve tied them all to ‘Tables’. Now I can unbutton the top button on my pants and sleep soundly….at my desk.
This is so simple I feel dumb for not knowing it.
Spice rack? Sorrel I missed it. Sounds like a real Wild Thyme. Did any of them take off their Cloves?
Jeff: Ha! Very good.
Thanks, sage.
I saw this on the weekend & thought “that’s awesome – solves a problem which is always annoying”. I just tried it at work though, and it seems that converting to the range in Excel 03 converts the header row to text, which means if the headings are numbers, the lookup functions don’t work!!
I tried converting the type to number & also multiplying the rogue text-numbers by 1 & pasting values back in, all to no avail!!
Any suggestions? Thanks!
Kate: I’m not sure I understand why the header row values would cause the vlookup functions to not work. VLOOKUPs typically use postioning and indexing to work (not values).
In any case, can you exclude the headers from the vlookup range? That is to say, don’t include them in the index range.