August 14, 2017 - by Bill Jelen
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:
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
Even if the Items table is on a different worksheet, the formula is the same, instead of the less readable
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!
- 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 the sample file here: Podcast2003.xlsx
Title Photo: makunin / pixabay