MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Readable References

August 14, 2017 - by Bill Jelen

Readable References

VLOOKUP is awesome and my favorite function

Not only do these tables make refreshing the data easier, they also make reading formulas much easier! The only thing you need to do is to press Ctrl + T before writing the formula.

Let’s go back to the VLOOKUP formula from above. This time, convert your items table and your purchase table to an Excel table with Ctrl + T right from the start! To make things easier, give each table a friendly name using the Table Tools tab:

Name Your Table
Name Your Table

Now type in the VLOOKUP again without doing anything differently than you normally do, your formula in C2 now is =VLOOKUP([@Item],Items,2,0) instead of =VLOOKUP(B2,$E$5:$F$10,2,0)!

Enter VLOOKUP Formula
Enter VLOOKUP Formula

Even if the Items table is on a different worksheet, the formula is the same, instead of the less readable =VLOOKUP(B2,Items!$A$2:$B$7,2,0).

The [@Item] in the formula refers to the cell in the Item column of this table (in the same row as the formula) and is therefore the same in the whole column. And Items refers to the whole items table (without the headers). Best of all, you don’t need to type any of this. Once this is a table, Excel will place these names in your formula as you select the cells/ranges!

Let’s take this one step further. Add another column to the Sales table to calculate the revenue with the formula =[@Price]*[@Qty]. If you now want to calculate the total revenue, the formula is =SUM(Sales[Revenue]); which is really easy to understand, no matter where the data is or how many rows it covers!

The Result
The Result

Watch Video

  • VLOOKUP is awesome and my favorite function
  • VLOOKUP haters complain that it is fragile due to the 3rd argument
  • If the shape of your lookup table changes the answers can change
  • One workaround is to replace third argument with MATCH
  • But imagine doing a MATCH for 1000 rows of VLOOKUP
  • Make your lookup table into a table before doing the VLOOKUP
  • The structured table reference will handle if the table shape changes
  • Plus it does not require doing a MATCH over and over
  • Peter Albert submitted this tip

Video Transcript

Learn Excel for MrExcel Podcast, Episode 2003 -- Readable References 

Don't forget to subscribe to the MrExcel XL playlist. I'll be podcasting this entire book.

Alright today's tip from Peter Albert. Peter Albert. Now let's talk about VLOOKUP. I am a huge VLOOKUP fan. For me VLOOKUP is the dividing line. If you can do VLOOKUPs, everything else in Excel is going to be easy for you. So VLOOKUP lets us look up the price from that table and we'll be talking about VLOOKUPs more later.

So copy this down and everything works alright but I have to tell you. I've seen them. I've talked to them. I've met them. There are VLOOKUP haters out there. People who hate if you look up and what other complaints is that it is so fragile, that third argument, where we said we want the third column, that if someone would decide later on that we need a new field here, maybe like, size. Alright, first off, there's appears to be some sort of a bug that Excel is not recalculating that whole thing. Let me undo, undo and then redo. There we go. That's weird, I got to report that to the Excel team, but you see that where we were getting price, it's now getting color, because it was hard-coded to say they want the third column. Alright and what people do to work around this is this crazy thing with =MATCH. Go look for the word Price in the first row of the table, F4,0 and that will tell us the price at this point is the fourth column. So they'll actually do =VLOOKUP. We're looking up A104, in this table. F4 and then instead of hard-coding the number four they go do a MATCH and the MATCH is going to be locked down to the price. So F4, twice to put $ before the 1 and it's going to be looking through the first row of the table. Oops, F4 twice, comma, missed the comma. Alright press F4 here comma 0 for an exact match to the match and then comma falls for an exact match to the VLOOKUP. Yeah and hey this works great and here I only have six of them so it's no big deal.

See if I insert a new one it will automatically adjust and keep getting the price but just imagine if you had a thousand VLOOKUPs and every single VLOOKUP is going to go redo that match to figure out that prices in the fifth column or fourth column. It's horrible. Tables simply solve this problem. So here's my VLOOKUP table, be it long before I do anything, I'm going to go here and CTRL T to make it into a real table. They're going to call it table 1 but I'm going to call it ProductTable, all one word, no spaces: ProductTable. So now it has a name. Alright, so now we have a table named ProductTable. Then we come over here and say we're going to do =INDEX of those prices. Which price we want? We want the result from the match of A104 into these items. Exact match, close parentheses for the INDEX. This is only doing a single match. It's not doing a match and a VLOOKUP. Kind of, will be much, much faster. Copy that down. Alright and then later on if we insert the size, so insert column, size everything continues to work because it's looking for the column called Price and let's say that if we change this to List Price, that formula gets rewritten. Right, so much, much safer, safer way to go.

Alright, so many cool tricks in tables. Check out this book from Kevin Jones and Zach Barresse on Excel Tables. All kinds of tricks in there and everything that we're podcasting in August and September is in this jam-packed book. Plus a lot of fun. Excel jokes. Excel cocktails. Excel tweets. Excel adventures. Jam-packed in full color. Check it out, buy this book. I would really appreciate it.

Alright today's episode. VLOOKUP is awesome and it's my favorite function but there's VLOOKUP haters out there that complain that it's fragile due to that third argument, if the shape of your table VLOOKUP table changes, the answers will change. One workaround is to replace that third argument with a MATCH, but jeez, imagine doing a MATCH for a thousand rows of VLOOKUP. So make your VLOOKUP into a table before doing the VLOOKUP. The Structure Table References will handle if the Table Shape changes. Plus you're not doing a VLOOKUP and a match. Just a single match along with an INDEX and INDEX is lightning, lightning fast.

Thanks to Peter Robert for this tip and thanks to you for stopping by. We'll see you next time, for another netcast from MrExcel.

Download File

Download the sample file here: Podcast2003.xlsx

Title Photo: makunin / pixabay

Bill Jelen is the author / co-author of
Microsoft Excel 2019 VBA and Macros

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.