VLookup In Plain English

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Mar 26, 2012.
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.
maxresdefault.jpg


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!
 

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top