Learn Excel 97 through Excel 2007 from Mr. Excel The Podcast
Host: Bill Jelen
Hey welcome back to the Mr. Excel net cast. I’m Bill Jelen. Basically, we start out with massive amounts of data so how are we going to analyze this. We’ll let’s fire up a pivot table, if you can solve this problem.
Hey welcome back to the Mr. Excel Net Cast I’m Bill Jelen. Well I have decided it’s the VLOOKUP week. Let’s talk about Excel’s most important function. Today we’ll cover the basics of VLOOKUP. The rest of the week we’ll take a look at some other things and in the left hand side here I have data set that came from my IT department.
How can I tell it came from the IT department well because this is exactly what I ask for but not necessarily useful. I said hey guys, I need item, date and quantity and that’s what they gave me but they gave me item number not item description. I don’t know what the item numbers are I need descriptions but I can’t go back to my IT folks and say hey run this again because they will realize I asked for the wrong thing, right. It will take them months to get to it.
I searched around my computer and I found this nice little table over here on the right hand side maps, SKU and description. So what I want to have happened is I need Excel to go through and find that item BG33-8 and look through the left most column of this table there it is right there and when it finds it I want it to return some value from that row in this particular case I’m going to ask for the second column from that row. So here’s the basics equal VLOOKUP. V by the way stands for vertical when I look for that item over there and A2 and then out of here.
We will do control shift down arrow. Shift right arrow to select that whole range L3 to M30 now. You got to make sure that that gets locked down so I press F4, put dollar sign throughout, which column do we want well, they are numbered column one is L column tow is M and that’s the one too and then say it with me. What do we have to put at the end of every single VLOOKUP that’s right, false, whenever I had to do that and notice but we have to do an Excel, otherwise it’s going to give us a close match.
Double click to send it down and Bam it fills in all those descriptions all the way through, very cool. One of the most powerful functions, I used to be the manager of financial analysis and when were hiring a new financial analyst I had one requirement can do VLOOKUPs in your sleep but if you could do VLOOKUP everything else was a piece of cake that’s the basics.
Now tomorrow we’ll take a look at what can go wrong better ways to do VLOOKUPs and so on. Thanks for stopping by I will see you next time for another Net Cast from Mr. Excel.
For 376 more tips, look for this book at bookstores or at Mr. Excel.com
A production of Holy Macro! Books
Transcription by:
Scribe4you Transcription Services