VLOOKUP is my favorite function in Excel. In Episode 1120, we will take a look at the basics of VLOOKUP.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, I've decided it's 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.
On the left-hand side here I have a data set, came from my IT department.
How can I tell it came from the IT department?
Well, because it's exactly what I asked 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'll realize I asked for the wrong thing, right, it'll take them months to get to it.
So I searched around on my computer, I found this nice little table over here on the right-hand side, SKU and description.
So what I want to have happen, is I need Excel to go through and find that item BG33-8, and look through the leftmost 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, =VLOOKUP, V by the way stands for vertical, we're going to look for that item over there in A2.
And then out here, we will do Ctrl+Shift+Down arrow, Shift+Right arrow to select that whole range, L3:M30.
Now, got to make sure that gets locked down, so I press F4, put dollar signs throughout, comma, which columns we want?
Well, they're numbered, column 1 is L, column 2 is M, that's the 2, and then say it with me: What do we have to put at the end of every single VLOOKUP?
That's right, ,FALSE ! We never had to do that in Lotus, but we have to do in Excel, otherwise it's going to give us a close match.
Double-click to send it down and bam, fills in all those descriptions all the way through.
Very cool, one of the most powerful functions.
I used to be a manager of financial analysis, and when we were hiring a new financial analyst, I had one requirement: “Can do VLOOKUPs in your sleep.” If you could do VLOOKUP, everything else was a piece of cake.
So, 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, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, I've decided it's 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.
On the left-hand side here I have a data set, came from my IT department.
How can I tell it came from the IT department?
Well, because it's exactly what I asked 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'll realize I asked for the wrong thing, right, it'll take them months to get to it.
So I searched around on my computer, I found this nice little table over here on the right-hand side, SKU and description.
So what I want to have happen, is I need Excel to go through and find that item BG33-8, and look through the leftmost 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, =VLOOKUP, V by the way stands for vertical, we're going to look for that item over there in A2.
And then out here, we will do Ctrl+Shift+Down arrow, Shift+Right arrow to select that whole range, L3:M30.
Now, got to make sure that gets locked down, so I press F4, put dollar signs throughout, comma, which columns we want?
Well, they're numbered, column 1 is L, column 2 is M, that's the 2, and then say it with me: What do we have to put at the end of every single VLOOKUP?
That's right, ,FALSE ! We never had to do that in Lotus, but we have to do in Excel, otherwise it's going to give us a close match.
Double-click to send it down and bam, fills in all those descriptions all the way through.
Very cool, one of the most powerful functions.
I used to be a manager of financial analysis, and when we were hiring a new financial analyst, I had one requirement: “Can do VLOOKUPs in your sleep.” If you could do VLOOKUP, everything else was a piece of cake.
So, 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, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
