The VLOOKUP function is one of the most versatile functions in Excel you can use VLOOKUP to add item descriptions to a database containing item numbers. Episode 484 shows you how VLOOKUP works. This is the first in a series of podcasts to compare VLOOKUP with INDEX and MATCH.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I wanna take a couple of days here to talk about the difference between VLOOKUP and INDEX and MATCH.
Someone, send in this question and rather than try and talk about the whole thing in one day.
We're gonna talk about VLOOKUP today, talk about MATCH tomorrow and then how we can actually use that as part of an INDEX function on Friday.
So, VLOOKUP stands for Vertical Lookup.
It's one of the most versatile functions we have in Excel.
It's great for someone from the IT department sent you a file it has an item number, but you need an item description.
So, I have 5,000 rows of item numbers over here and somewhere on my computer I found a nice little range that has SKU or item number and the description and I need to match those two things up.
I'm gonna delete a few columns here or hide a few columns that we will be able to see that both things side by side on the iPod.
Basically, I have an item number in column A and I'm gonna build a VLOOKUP, =VLOOKUP first thing I need to specify is what I'm trying to look up.
So, that's the item number in column A type the comma and then the lookup table, the table array, the table array has to have the item number in the first column.
So, in our case that's going to be this table over here in column L and it's gonna extend from L37 to M64.
Now, as I copy this formula down my 5,000 rows of data.
I always want to make sure that I pointed rows 37 to 64 so at this point I'll hit the f4 key to put the dollar signs in, dollar signs in the references of course said that's an absolute reference.
Next we need to say which column in the lookup table we want.
Well, the first column is SKU number second column is the description that's the one.
Finally, we have to put the word false.
Now, if you're a scientist or a teacher or an engineer, you might be looking for the other kind of VLOOKUP.
Which looks for the range, but here we're looking for an exact match. If you put the word false, it forces Excel to give us an exact match.
I hit enter and sure enough it goes and finds the correct item number from the list and then gives me the description.
When I copy that formula down very quickly the VLOOKUP will go get the Item description for each of the SKU's in column A.
All right, I'll stop back tomorrow.
We'll talk about using the MATCH function.
The MATCH is similar to VLOOKUP, but has some other applications and there's times when you'll want to use match instead of VLOOKUP.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
I wanna take a couple of days here to talk about the difference between VLOOKUP and INDEX and MATCH.
Someone, send in this question and rather than try and talk about the whole thing in one day.
We're gonna talk about VLOOKUP today, talk about MATCH tomorrow and then how we can actually use that as part of an INDEX function on Friday.
So, VLOOKUP stands for Vertical Lookup.
It's one of the most versatile functions we have in Excel.
It's great for someone from the IT department sent you a file it has an item number, but you need an item description.
So, I have 5,000 rows of item numbers over here and somewhere on my computer I found a nice little range that has SKU or item number and the description and I need to match those two things up.
I'm gonna delete a few columns here or hide a few columns that we will be able to see that both things side by side on the iPod.
Basically, I have an item number in column A and I'm gonna build a VLOOKUP, =VLOOKUP first thing I need to specify is what I'm trying to look up.
So, that's the item number in column A type the comma and then the lookup table, the table array, the table array has to have the item number in the first column.
So, in our case that's going to be this table over here in column L and it's gonna extend from L37 to M64.
Now, as I copy this formula down my 5,000 rows of data.
I always want to make sure that I pointed rows 37 to 64 so at this point I'll hit the f4 key to put the dollar signs in, dollar signs in the references of course said that's an absolute reference.
Next we need to say which column in the lookup table we want.
Well, the first column is SKU number second column is the description that's the one.
Finally, we have to put the word false.
Now, if you're a scientist or a teacher or an engineer, you might be looking for the other kind of VLOOKUP.
Which looks for the range, but here we're looking for an exact match. If you put the word false, it forces Excel to give us an exact match.
I hit enter and sure enough it goes and finds the correct item number from the list and then gives me the description.
When I copy that formula down very quickly the VLOOKUP will go get the Item description for each of the SKU's in column A.
All right, I'll stop back tomorrow.
We'll talk about using the MATCH function.
The MATCH is similar to VLOOKUP, but has some other applications and there's times when you'll want to use match instead of VLOOKUP.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
