To finish up the recent discussion of VLOOKUP, INDEX, MATCH, and OFFSET, todays episode talks about the strange situation where the data that you want is located to the left of the key field in the lookup table. Episode 487 will show you how to use VLOOKUP and MATCH to solve this problem.
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.
The past few podcasts we've been talking about VLOOKUP, INDEX and MATCH.
and today i'm going to show you one more use of INDEX and MATCH.
This happens when the table array is not formatted properly.
Here we have an SKU and someone can come in using the data validation drop-down and choose a different SKU.
Our vlookup here in C4, gives us the description but somehow we need to be able to get the price for that item number and when you look at our table over here, you'll see that we have SKY in column L, the description in column M.
That's perfect to allow the VLOOKUP to work for the description but unfortunately the price is in the wrong spot.
It's in column K it's to the left of the key field which won't work for VLOOKUP.
Now most people just say.
Hey move the price from K over to N but let's say that there's some reason that we can't do that.
Let's say that this is a web query or something where we're getting that data in and it's in the wrong spot.
Is there way to VLOOKUP left?
I've always argued that microsoft would do great if they would allow us to say that we want the -1 column that would just completely solve this problem.
However they haven't done that so we have to work around it using a completely Unintuitive solution.
The unintuitive solution says, instead of using the VLOOKUP, we're going to use a MATCH function.
The MATCH function on the inside of this formula.
The match function says go find that SKU in C2 and look through column L, look through L2 to L29 and find an exact match that's what the comma FALSE or comma 0 and the match function does and that piece of the function is going to return a row number.
So let me choose this and hit the F9 key.
So we'll see how that actually works.
That says, hey that particular SKU is on the 7th row of the range.
Now the INDEX function, the INDEX function then says, we'll go take all the prices in K2 to K29.
Which row no do we want, we want the one that's on the 7th row of the range and then which column, comma 1 for the column.
We could have left that off.
The column number is actually optional especially when you only have one column in your original range.
Now the one thing to worry about when you're creating these INDEX and MATCH is both ranges have to start on the same row, so L2 to L29 where we're looking through for the SKU's.
I have to make sure the K2 to K29 is looking at the exact same range.
Sometimes there's a tendency to include the price heading and not the SKU heading.
That of course will lead to the wrong result.
So you always want to check them a few times before you go.
There you have it using INDEX and MATCH, to solve the problem of when your lookup data is to the left of the key field.
Hey thanks for stopping by. We'll see you next time for another netcast from Mr Excel.
I'm Bill Jelen.
The past few podcasts we've been talking about VLOOKUP, INDEX and MATCH.
and today i'm going to show you one more use of INDEX and MATCH.
This happens when the table array is not formatted properly.
Here we have an SKU and someone can come in using the data validation drop-down and choose a different SKU.
Our vlookup here in C4, gives us the description but somehow we need to be able to get the price for that item number and when you look at our table over here, you'll see that we have SKY in column L, the description in column M.
That's perfect to allow the VLOOKUP to work for the description but unfortunately the price is in the wrong spot.
It's in column K it's to the left of the key field which won't work for VLOOKUP.
Now most people just say.
Hey move the price from K over to N but let's say that there's some reason that we can't do that.
Let's say that this is a web query or something where we're getting that data in and it's in the wrong spot.
Is there way to VLOOKUP left?
I've always argued that microsoft would do great if they would allow us to say that we want the -1 column that would just completely solve this problem.
However they haven't done that so we have to work around it using a completely Unintuitive solution.
The unintuitive solution says, instead of using the VLOOKUP, we're going to use a MATCH function.
The MATCH function on the inside of this formula.
The match function says go find that SKU in C2 and look through column L, look through L2 to L29 and find an exact match that's what the comma FALSE or comma 0 and the match function does and that piece of the function is going to return a row number.
So let me choose this and hit the F9 key.
So we'll see how that actually works.
That says, hey that particular SKU is on the 7th row of the range.
Now the INDEX function, the INDEX function then says, we'll go take all the prices in K2 to K29.
Which row no do we want, we want the one that's on the 7th row of the range and then which column, comma 1 for the column.
We could have left that off.
The column number is actually optional especially when you only have one column in your original range.
Now the one thing to worry about when you're creating these INDEX and MATCH is both ranges have to start on the same row, so L2 to L29 where we're looking through for the SKU's.
I have to make sure the K2 to K29 is looking at the exact same range.
Sometimes there's a tendency to include the price heading and not the SKU heading.
That of course will lead to the wrong result.
So you always want to check them a few times before you go.
There you have it using INDEX and MATCH, to solve the problem of when your lookup data is to the left of the key field.
Hey thanks for stopping by. We'll see you next time for another netcast from Mr Excel.
