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

Download File

Download the sample file here: Podcast2003.xlsx

Title Photo: makunin / pixabay

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.