Bill starts from the beginning; why to use a VLOOKUP, when to use a VLOOKUP and how to Use a VLOOKUP. If you are ready to unravel the mystery of VLOOKUP, follow along with Episode #1533 as Bill Starts off VLOOKUP WEEK 2012 with a comprehensive how-to.
Welcome toVLOOKUP WEEK 2012! What is VLOOKUP WEEK, you ask? VLOOKUP WEEK is an entire week [March 25th, 2012 through March 31st, 2012] dedicated to one of the greatest yet least used Functions of Microsoft Excel.
Welcome toVLOOKUP WEEK 2012! What is VLOOKUP WEEK, you ask? VLOOKUP WEEK is an entire week [March 25th, 2012 through March 31st, 2012] dedicated to one of the greatest yet least used Functions of Microsoft Excel.
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen from MrExcel.com.
This is VLOOKUP week.
Yes, March 25 through to the 31st.
This is our first episode of the special series of the VLOOKUP themed webcast.
This is Episode 1533 - VLOOKUP in plain English.
People say to me: "Can you just explain what it does in plain English?
It seems so confusing".
And here's a great example of how it works.
I have a model here. And for each row - hundreds of rows of data, we have a product line A through H and I need to calculate a bonus.
The bonus is based on a rate that's based on the line.
Think about this IF Statement.
This IF Statment would be horrible.
Equal IF this is equal to A and then it's 0.05 comma IF - Oh my gosh this would just be huge right?
So, VLOOKUP can solve this.
Here's a little table than maps line to rate.
Contrary to popular belief, the table does not have to be sorted, because we're looking for exact matches here.
So it says equal VLOOKUP, go look up this line A and this table over here.
Alright, so N2 to P9 is our table.
I'm going to press F4 right now.
That puts dollar signs in throughout.
The only dollar signs I really need in this case are before the 2 and before the 9.
Always I copy that format down that always points to row 2 through 9, but I'll leave all the dollar signs there because its easier.
Comma, which column do I want?
Well this is column 1, column 2, column 3.
I want the third column.
And then at the end of every VLOOKUP that you create, we don't want approximate matches we want the exact match.
So 99.9% of the time, if you're in accounting or finance, you're going to be doing the exact match.
Double click to fill it in.
We're going to shoot that down so E is 5%, E is 5% And it doesn't matter, again that this table is sort of doing the comma False at the end.
This table can be in any order and it's going to work.
Well hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Hey, just a special program note.
On Thursday CFO magazine is joining in with VLOOKUP week.
They're doing an entire 75 minute webcast Excel's VLOOKUP function - learn, design and trouble shoot.
If you go to cfo.com - click on webcast.
You'll see that there.
I have set up an entire agenda there from your very first VLOOKUP through all sorts of hard VLOOKUPS.So check that out!
I'm Bill Jelen from MrExcel.com.
This is VLOOKUP week.
Yes, March 25 through to the 31st.
This is our first episode of the special series of the VLOOKUP themed webcast.
This is Episode 1533 - VLOOKUP in plain English.
People say to me: "Can you just explain what it does in plain English?
It seems so confusing".
And here's a great example of how it works.
I have a model here. And for each row - hundreds of rows of data, we have a product line A through H and I need to calculate a bonus.
The bonus is based on a rate that's based on the line.
Think about this IF Statement.
This IF Statment would be horrible.
Equal IF this is equal to A and then it's 0.05 comma IF - Oh my gosh this would just be huge right?
So, VLOOKUP can solve this.
Here's a little table than maps line to rate.
Contrary to popular belief, the table does not have to be sorted, because we're looking for exact matches here.
So it says equal VLOOKUP, go look up this line A and this table over here.
Alright, so N2 to P9 is our table.
I'm going to press F4 right now.
That puts dollar signs in throughout.
The only dollar signs I really need in this case are before the 2 and before the 9.
Always I copy that format down that always points to row 2 through 9, but I'll leave all the dollar signs there because its easier.
Comma, which column do I want?
Well this is column 1, column 2, column 3.
I want the third column.
And then at the end of every VLOOKUP that you create, we don't want approximate matches we want the exact match.
So 99.9% of the time, if you're in accounting or finance, you're going to be doing the exact match.
Double click to fill it in.
We're going to shoot that down so E is 5%, E is 5% And it doesn't matter, again that this table is sort of doing the comma False at the end.
This table can be in any order and it's going to work.
Well hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
Hey, just a special program note.
On Thursday CFO magazine is joining in with VLOOKUP week.
They're doing an entire 75 minute webcast Excel's VLOOKUP function - learn, design and trouble shoot.
If you go to cfo.com - click on webcast.
You'll see that there.
I have set up an entire agenda there from your very first VLOOKUP through all sorts of hard VLOOKUPS.So check that out!
