Today is our last podcast in our first "Don't Fear The Spreadsheet" series.
Tyler asks: "I hear and see all of this information about VLOOKUP - What is is and what is the big deal? Follow along with Bill in Episode #1646 to learn what VLOOKUP is, what it can do and why you will find it more useful than you imagined. Bill Also covers a site devoted to VLOOKUP as well as other resources.
If you are just starting out in Excel or you want to go back and find the things that you missed when you started using Excel, then "Don't Fear the Spreadsheet" is the 'Beginner Oriented' Excel How-To book for you. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Check out "Don't Fear the Spreadsheet". Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Tyler asks: "I hear and see all of this information about VLOOKUP - What is is and what is the big deal? Follow along with Bill in Episode #1646 to learn what VLOOKUP is, what it can do and why you will find it more useful than you imagined. Bill Also covers a site devoted to VLOOKUP as well as other resources.
If you are just starting out in Excel or you want to go back and find the things that you missed when you started using Excel, then "Don't Fear the Spreadsheet" is the 'Beginner Oriented' Excel How-To book for you. Why are we so confident? Because the questions in this book were asked by an absolute Excel Beginner -- Tyler Nash -- with the answers provided by three (3) Microsoft MVPs specializing in and dedicated to teaching others Microsoft Excel, from the ground up -- Kevin Jones, Tom Urtis and Bill Jelen. Check out "Don't Fear the Spreadsheet". Don't Fear the Spreadsheet
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Don't Fear the Spreadsheet podcast episode number 20: VLOOKUP.
Hey, here we are again.
This is the last Don’t Fear The Spreadsheet podcast we have scheduled.
We had planned 20 episodes to go along with the book.
I hope you have enjoyed this far.
Last question, here's Tyler Nash.
Tyler: I just don't understand.
Everyone's always making such a big deal about VLOOKUPs.
VLOOKUP this and VLOOKUP that.
There was even a VLOOKUP week.
I feel left in the dark.
What is a VLOOKUP and how can I use one?
Bill: Hi Tyler.
VLOOKUP is my favorite function in all of Excel.
I'm glad you asked this question.
VLOOKUP will save you a massive amount of work.
So, here is a big list of a hundred items that we sold.
All right, there's item number, date, quantity, but they didn't put the description in and you found this little list here that maps SKU and description.
So, what are you going to do?
All right, W25-6, let's go find it.
There it is.
We're going to copy Ctrl C and paste.
All right, CR 50-4.
Oh, we're lucky there because there's two of them in a row.
CR 50-4, copy, paste, paste.
BR 26-3.
All right, that's right there.
Copy and paste.
Now, you can get this whole thing done probably in about 45 minutes or so.
It's not going to be horrible, although if you had a thousand rows, it’d be all day.
At that point, you might, you know, start to think about ways to make it faster like sorting this.
That way, once you found CR 50-4, you can just paste it in a nice contiguous range, but VLOOKUP makes that completely unnecessary.
VLOOKUP does what you were just doing manually.
It says, “Hey go look for W25-6 over here in this table at the leftmost column,” the table has to have what you're looking for, “and when you find it, give me the first second column.” Second column.
Now, there's a weird form of VLOOKUP that's used by scientists and commission accountants where it'll look up numbers and a range.
We need to say that we're not doing that type.
We're looking for an exact match.
So, VLOOKUP is looking up this value over here, comma and then I'll move this out of the way.
Thanks, Mike Girvin for that trick.
I'll choose my range here.
Okay, now, I need that range to stay exactly the way it is as I copy the formula down.
So, I'm going to press the F4 key which puts dollar signs in.
Now, we talked about that in the book but we haven't talked about it in the Don't Fear The Spreadsheet podcast yet.
This locks this range though we-- as we copy it down it always points to that table and then, which column we want?
We want column 2 and then false to say, hey, we're not doing the range match, the weird science application.
We're just doing regular VLOOKUPs.
There's our 18-karat Italian Gold Women’s Watch.
Now, which was faster?
Copy and paste or VLOOKUP?
Well, probably copy and paste the first time.
The beautiful thing though, double-click to copy that formula down and it just filled them all in.
Even if we had 10,000 rows, it would fill it in in just a couple of seconds.
VLOOKUP can save you so much tedious work by doing LOOKUPs very, very quickly.
It's a great function.
Tyler: Thanks for stopping by.
We'll see you next episode.
Check out Don't Fear The Spreadsheet.
This book makes Excel for Dummies look like it was written for rocket scientists.
Don't Fear the Spreadsheet podcast episode number 20: VLOOKUP.
Hey, here we are again.
This is the last Don’t Fear The Spreadsheet podcast we have scheduled.
We had planned 20 episodes to go along with the book.
I hope you have enjoyed this far.
Last question, here's Tyler Nash.
Tyler: I just don't understand.
Everyone's always making such a big deal about VLOOKUPs.
VLOOKUP this and VLOOKUP that.
There was even a VLOOKUP week.
I feel left in the dark.
What is a VLOOKUP and how can I use one?
Bill: Hi Tyler.
VLOOKUP is my favorite function in all of Excel.
I'm glad you asked this question.
VLOOKUP will save you a massive amount of work.
So, here is a big list of a hundred items that we sold.
All right, there's item number, date, quantity, but they didn't put the description in and you found this little list here that maps SKU and description.
So, what are you going to do?
All right, W25-6, let's go find it.
There it is.
We're going to copy Ctrl C and paste.
All right, CR 50-4.
Oh, we're lucky there because there's two of them in a row.
CR 50-4, copy, paste, paste.
BR 26-3.
All right, that's right there.
Copy and paste.
Now, you can get this whole thing done probably in about 45 minutes or so.
It's not going to be horrible, although if you had a thousand rows, it’d be all day.
At that point, you might, you know, start to think about ways to make it faster like sorting this.
That way, once you found CR 50-4, you can just paste it in a nice contiguous range, but VLOOKUP makes that completely unnecessary.
VLOOKUP does what you were just doing manually.
It says, “Hey go look for W25-6 over here in this table at the leftmost column,” the table has to have what you're looking for, “and when you find it, give me the first second column.” Second column.
Now, there's a weird form of VLOOKUP that's used by scientists and commission accountants where it'll look up numbers and a range.
We need to say that we're not doing that type.
We're looking for an exact match.
So, VLOOKUP is looking up this value over here, comma and then I'll move this out of the way.
Thanks, Mike Girvin for that trick.
I'll choose my range here.
Okay, now, I need that range to stay exactly the way it is as I copy the formula down.
So, I'm going to press the F4 key which puts dollar signs in.
Now, we talked about that in the book but we haven't talked about it in the Don't Fear The Spreadsheet podcast yet.
This locks this range though we-- as we copy it down it always points to that table and then, which column we want?
We want column 2 and then false to say, hey, we're not doing the range match, the weird science application.
We're just doing regular VLOOKUPs.
There's our 18-karat Italian Gold Women’s Watch.
Now, which was faster?
Copy and paste or VLOOKUP?
Well, probably copy and paste the first time.
The beautiful thing though, double-click to copy that formula down and it just filled them all in.
Even if we had 10,000 rows, it would fill it in in just a couple of seconds.
VLOOKUP can save you so much tedious work by doing LOOKUPs very, very quickly.
It's a great function.
Tyler: Thanks for stopping by.
We'll see you next episode.
Check out Don't Fear The Spreadsheet.
This book makes Excel for Dummies look like it was written for rocket scientists.
