When you use Ctrl+T tables, the references in formulas are easier to read. 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
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
Transcript of the video:
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 ****tails.
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
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 ****tails.
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
