Can the VLOOKUP return a value one row below the matched value? Episode 854 shows you how to achieve this result using two other functions.
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, alright, hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Boy, a great question that came in.
This was from a seminar date out in Springfield, Missouri, last week, down at the IMA Council meeting down there.
Someone asked about VLOOKUP but it was the most unusual VLOOKUP question I've ever had.
They said, "I want to do a VLOOKUP, but when it finds the matching value, I don't want something from that row; I need something from the next row." Okay, so, VLOOKUP generally will look for a value-- in this case, the word "Reds" in A1-- and search through the first column of a Lookup table.
So here it would find "Reds" and then, because we have ,3 it would give us the third column.
So here's Column 1, Column 2, Column 3-- the FALSE says no close matches... if you don't find it, an exact number, give us an NA.
So when we click Enter here, we get the NL for, basically, from Cell H14, but this person needed to grab the value from the next row.
Well, if you mastered VLOOKUP, then it's going to be very easy for you to understand MATCH.
We'll use =MATCH, and basically MATCH says, "Hey, go look for that value in A1, and we want you to look through this single column or single row." And just like in VLOOKUP, we have to put the word FALSE, although the help for MATCH says don't put FALSE, put a 0.
So we put a 0, and what it's going to do is it's going to tell us the row number where that item is found.
Now, you know, that's going to seem kind of strange, here's rows 2, 4, 6, 8, 10, 12, 13-- so I'm going to expect a 13 as the result of this.
Alright, so, it's saying that the word "Reds" is on the 13th row within that range.
When I read the help for this, I said that's a bizarre function-- when would I ever need to know that something falls on the 13th row?
Well, in and of itself, it's not the most interesting thing, but there's a great function called INDEX.
INDEX says, "Hey, you have a rectangular range over here, it can either be a single column, a single row, or a rectangular range of many rows and columns.
You specify the range and say what row number do I want to grab." Well, in this case, we want to grab the result of the MATCH, plus 1.
So I'll use Cell B5+1 and then at this point, you would specify which Cell number you want.
So you have a couple of choices here, you put ,1 because it's the first column of that range, or you can just leave that off, which I'll do.
I'll hit the parentheses, I'll leave that off.
And we'll see that the Reds are in the Central division of the NL.
So, interesting.
Can you do a VLOOKUP that grabs the next value?
Well, you can't actually use a VLOOKUP, but you can use a combination of INDEX and MATCH.
Now that we've built this in a couple of cells, let's go through and actually make it all be a single formula.
So, I edit the cell, grab everything with the equal sign, hit Ctrl+C, and then here in the formula we're at B5, I'm going to paste-- Ctrl+V to put that formula in-- and we can actually get rid of those intermediate results there in row 5.
So, great question.
I love when I get a brand new question, one that I've never heard before-- can you do VLOOKUP to grab a value on the next row?
And, in fact, you can and I already hear the email coming in.
Yeah, I agree that this set up over here is a horrible way to set up this data; in real life, we should have the league in Column G and the division in Column H, but sometimes we get data from people and we don't have control.
And if you're getting this data feed every day someone's creating it, well, it might be easier just to create a formula that will deal with it, rather than having to rearrange the data every single day.
So there you have it.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Boy, a great question that came in.
This was from a seminar date out in Springfield, Missouri, last week, down at the IMA Council meeting down there.
Someone asked about VLOOKUP but it was the most unusual VLOOKUP question I've ever had.
They said, "I want to do a VLOOKUP, but when it finds the matching value, I don't want something from that row; I need something from the next row." Okay, so, VLOOKUP generally will look for a value-- in this case, the word "Reds" in A1-- and search through the first column of a Lookup table.
So here it would find "Reds" and then, because we have ,3 it would give us the third column.
So here's Column 1, Column 2, Column 3-- the FALSE says no close matches... if you don't find it, an exact number, give us an NA.
So when we click Enter here, we get the NL for, basically, from Cell H14, but this person needed to grab the value from the next row.
Well, if you mastered VLOOKUP, then it's going to be very easy for you to understand MATCH.
We'll use =MATCH, and basically MATCH says, "Hey, go look for that value in A1, and we want you to look through this single column or single row." And just like in VLOOKUP, we have to put the word FALSE, although the help for MATCH says don't put FALSE, put a 0.
So we put a 0, and what it's going to do is it's going to tell us the row number where that item is found.
Now, you know, that's going to seem kind of strange, here's rows 2, 4, 6, 8, 10, 12, 13-- so I'm going to expect a 13 as the result of this.
Alright, so, it's saying that the word "Reds" is on the 13th row within that range.
When I read the help for this, I said that's a bizarre function-- when would I ever need to know that something falls on the 13th row?
Well, in and of itself, it's not the most interesting thing, but there's a great function called INDEX.
INDEX says, "Hey, you have a rectangular range over here, it can either be a single column, a single row, or a rectangular range of many rows and columns.
You specify the range and say what row number do I want to grab." Well, in this case, we want to grab the result of the MATCH, plus 1.
So I'll use Cell B5+1 and then at this point, you would specify which Cell number you want.
So you have a couple of choices here, you put ,1 because it's the first column of that range, or you can just leave that off, which I'll do.
I'll hit the parentheses, I'll leave that off.
And we'll see that the Reds are in the Central division of the NL.
So, interesting.
Can you do a VLOOKUP that grabs the next value?
Well, you can't actually use a VLOOKUP, but you can use a combination of INDEX and MATCH.
Now that we've built this in a couple of cells, let's go through and actually make it all be a single formula.
So, I edit the cell, grab everything with the equal sign, hit Ctrl+C, and then here in the formula we're at B5, I'm going to paste-- Ctrl+V to put that formula in-- and we can actually get rid of those intermediate results there in row 5.
So, great question.
I love when I get a brand new question, one that I've never heard before-- can you do VLOOKUP to grab a value on the next row?
And, in fact, you can and I already hear the email coming in.
Yeah, I agree that this set up over here is a horrible way to set up this data; in real life, we should have the league in Column G and the division in Column H, but sometimes we get data from people and we don't have control.
And if you're getting this data feed every day someone's creating it, well, it might be easier just to create a formula that will deal with it, rather than having to rearrange the data every single day.
So there you have it.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
