In this Dueling Excel Episode, Find a value in this row and then get the values 1 and 2 columns to the right of that value. Mike and Bill duel it out in Episode #1188.
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun is standing right here next to me, live from Highline Community College in Seattle.
Wow, today's question sent in by Ednen or luvbite38 at YouTube.
We have a lookup value here, we have to go over to the adjacent row, find the lookup value, and then grab the number that is 1 to the right, and the number that is 2 to the right.
OK so, here's what I'm going to do, I'm going to start to build this using the =MATCH, go find this value, I want to press F4 3 times, there we go, and go look for it over here, and ,0 . Right and that's going to tell me that Link is in the 4th column.
If I put in 12D, then it's in the 3rd column.
Alright, so that's good, that's working great.
What I want to do is then take the index of that whole range over there, have to get out of the way.
And we want to press F4 3 times to freeze the columns but not the rows, comma, and then which row, well that's row #1 because there's only one row, and then the column that we want +1 to get the column just to the right of it.
And good, that's working.
Take that whole big long thing, Ctrl+C over here, Ctrl+V, at this time we want +2, two equal signs at the beginning, let's get rid of one of those, and 19!
Let's do a little test, we’ll change that to be “ww”, and we should get 12D and Link, beautiful!
Alright Mike, what have you got?
Mike: Thanks MrExcel!
I'm going to come down here, same little data set, I'll highlight everything to all the formulas at once.
The active cell right there, I'll hit =, and I'm going to use the same INDEX, and I'm going to take this array right here.
And we will hit F4 3 times, because when we lock it over here, it needs to be locked there.
But when we go down, boom, it needs to move to the next row, comma, and then I'm going to use the same MATCH of this.
We're looking up Link, 3 times with the F4, get the same range right here, I'm just going to copy it, Ctrl+C, and paste, ,0 , we're looking up an exact look up here for MATCH.
And now, as MrExcel showed, when we got the Link, it gave us a 4, but I really need to +1, and when I copy it over one again, I needed to +2, so I'm going to do +COLUMNS.
COLUMNS is a great, if can get the function right here, is a great number incrementer inside of a formula.
So I'm sitting in B9, so I'm going to say $B9:B9).
Right, now it's saying columns B-B, how many is that?
One!
This is locked, this is not, so it goes over one, it'll say B-C, which is 2.
Got to get rid of that = there.
Now, what in the world am I doing here?
This is a column number, and I'm putting in the row number.
Well MATCH, if there is only columns here, it will interpret row as columns, so that'll work just fine, I'm going to Ctrl+Enter.
Notice the active cell has the formula, when I Ctrl+Enter, it will populate all the cells.
Let's try a little test here, how about “ww”, and it looks, boom, boom, and down here it looks like it's working also, 22.
Alright, hand it back to MrExcel!
Bill: Alright, there you have it.
Hey, I want to thank you for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey welcome back, it's another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, Mike Girvin from ExcelIsFun is standing right here next to me, live from Highline Community College in Seattle.
Wow, today's question sent in by Ednen or luvbite38 at YouTube.
We have a lookup value here, we have to go over to the adjacent row, find the lookup value, and then grab the number that is 1 to the right, and the number that is 2 to the right.
OK so, here's what I'm going to do, I'm going to start to build this using the =MATCH, go find this value, I want to press F4 3 times, there we go, and go look for it over here, and ,0 . Right and that's going to tell me that Link is in the 4th column.
If I put in 12D, then it's in the 3rd column.
Alright, so that's good, that's working great.
What I want to do is then take the index of that whole range over there, have to get out of the way.
And we want to press F4 3 times to freeze the columns but not the rows, comma, and then which row, well that's row #1 because there's only one row, and then the column that we want +1 to get the column just to the right of it.
And good, that's working.
Take that whole big long thing, Ctrl+C over here, Ctrl+V, at this time we want +2, two equal signs at the beginning, let's get rid of one of those, and 19!
Let's do a little test, we’ll change that to be “ww”, and we should get 12D and Link, beautiful!
Alright Mike, what have you got?
Mike: Thanks MrExcel!
I'm going to come down here, same little data set, I'll highlight everything to all the formulas at once.
The active cell right there, I'll hit =, and I'm going to use the same INDEX, and I'm going to take this array right here.
And we will hit F4 3 times, because when we lock it over here, it needs to be locked there.
But when we go down, boom, it needs to move to the next row, comma, and then I'm going to use the same MATCH of this.
We're looking up Link, 3 times with the F4, get the same range right here, I'm just going to copy it, Ctrl+C, and paste, ,0 , we're looking up an exact look up here for MATCH.
And now, as MrExcel showed, when we got the Link, it gave us a 4, but I really need to +1, and when I copy it over one again, I needed to +2, so I'm going to do +COLUMNS.
COLUMNS is a great, if can get the function right here, is a great number incrementer inside of a formula.
So I'm sitting in B9, so I'm going to say $B9:B9).
Right, now it's saying columns B-B, how many is that?
One!
This is locked, this is not, so it goes over one, it'll say B-C, which is 2.
Got to get rid of that = there.
Now, what in the world am I doing here?
This is a column number, and I'm putting in the row number.
Well MATCH, if there is only columns here, it will interpret row as columns, so that'll work just fine, I'm going to Ctrl+Enter.
Notice the active cell has the formula, when I Ctrl+Enter, it will populate all the cells.
Let's try a little test here, how about “ww”, and it looks, boom, boom, and down here it looks like it's working also, 22.
Alright, hand it back to MrExcel!
Bill: Alright, there you have it.
Hey, I want to thank you for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
