A viewer from Portugal sends in a question about finding which row contains the match. Episode 1139 shows you a few ways to go!
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:
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, we have Mike Girvin from ExcelIsFun, I even have Mike's three-year-old son Isaac.
Did you see his hilarious video on YouTube?
Isaac actually put some cool tips out there, just randomly clicking around.
Watch out Mike, you're going to have some competition there.
So hey, we have a question, they sent in from Portugal.
And what they have is, they have this matrix over here, a little drop-down where you can choose one of the columns, p1 p2 p3, and when we choose that we need to figure out where the X is, and then return the value over here along the left-hand side.
So I'm going to build this in a couple of steps, let's talk about how I build it.
First thing I want to do is figure out when they choose p2 up there, which column is that in.
So =MATCH, go find this p2 within this range over here, B1:D1, and we want an exact match, so we put zero, and that says “Alright, we're in column 2.” And if we would choose something different, p1 column 1, or p3 column 3.
Now I want to point to the correct column, I'm going to use the OFFSET function to do that, OFFSET says “Hey, start over here in A2.” And OFFSET does lots of things, we're not going to use them all here.
How many rows to go down now, don't need to go any rows down.
How many columns to go over, oh yeah, we're going to use this one right here, the result of that MATCH.
How many rows tall we want, we want 3 rows tall, and how many columns wide, 1 column wide.
Now OFFSET is going to return all three cells in the column, which is not what we want to do.
So I'm going to copy those characters to the clipboard, everything except for the equal sign, we're going to use that here.
=MATCH, go find the x within, look up array, well that's where I paste in that OFFSET.
So it's going to return the values from that correct column and then, of course, an exact match, and what this does is it says “Hey, if you've chosen p3, x is in the second row.” Now we can test this out here, we choose p1, x is in the first row, choose p2, x is in the third row.
Alright, so then, value on the left-hand side, we’ll use the INDEX function, INDEX of what's over here, A2:A4, comma, which row we want, it's that row right there.
Alright, and so we get M3, there's our answer.
Now of course, I took all four of those pieces and put them back together to create this one really long formula up here, that does all of that in one formula.
So that's how I would solve it, Mike, will send over, you consult with Isaac, see what you guys come up with, be right back!
Mike: Thanks MrExcel!
Hey, Isaac said that he wants to use the INDEX function instead of OFFSET, so that's what we'll do, alright, I think of this kind of as a 3-way look up.
We have something here, we want a formula here, that's going to look here, find the position, then go down here, extract a whole column, that'll be the second look up.
First look up is finding the p-whatever, look up here, then using the x to then jump over to this third item, so there's like a 3-way look up.
First thing is, I'm just going to see if I can get the column number with the MATCH, just like MrExcel.
We take that, whatever is in that cell, look through there, ,0 , because it's an exact match, and that will give us 1.
If I change this to 2, then it gives me the 2nd column.
Now the next thing is, we need to somehow extract, right?
Now we need to extract this range of values, and then look in that range, find the x, which will deliver the row number to find the particular item over here.
MrExcel used the OFFSET, Isaac said you wanted to use the INDEX, so we'll try that.
INDEX, and the INDEX needs the array, got to do the whole array here, inside the table.
Now comma, and the whole trick here to get the index to extract a column, is to use row number 0.
Remember, we already have the column number right here from the match, but that 0 there means, in essence “Once you find the column, give me all the rows.” And that's what it does, it gives us all the rows at that particular column in that whole table there.
So now I’m going to close parenthesis, if I were to highlight this and hit the F9, key you can see it gives me exactly that range, I'm going to Ctrl+Z.
Now, that INDEX is going to be used inside of MATCH, because all it's doing is delivering the range, so I say MATCH.
And what do I want to look up? “x”, now I want x there, comma, the lookup array is that INDEX, and then I come to the end, and the match type is 0 because we're looking up an exact match.
So there it is, that delivers the 2, that's the row number that we then need to use in one more INDEX!
Because Remember, we're looking up here, so we have that whole MATCH INDEX MATCH thing, gives us the row number.
So now we just do INDEX of this right here, comma, that is the row number, we come to the end, close parentheses, BOOM, there it is, M2.
Alright, so we pick the p1 and we get that, we pick up p3, obviously this is a little data set, the big data set is what this was really going to be used for.
So there you go, we'll see you next trick!
Bill: Hey Mike, entertaining as always.
I want to thank everyone 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, we have Mike Girvin from ExcelIsFun, I even have Mike's three-year-old son Isaac.
Did you see his hilarious video on YouTube?
Isaac actually put some cool tips out there, just randomly clicking around.
Watch out Mike, you're going to have some competition there.
So hey, we have a question, they sent in from Portugal.
And what they have is, they have this matrix over here, a little drop-down where you can choose one of the columns, p1 p2 p3, and when we choose that we need to figure out where the X is, and then return the value over here along the left-hand side.
So I'm going to build this in a couple of steps, let's talk about how I build it.
First thing I want to do is figure out when they choose p2 up there, which column is that in.
So =MATCH, go find this p2 within this range over here, B1:D1, and we want an exact match, so we put zero, and that says “Alright, we're in column 2.” And if we would choose something different, p1 column 1, or p3 column 3.
Now I want to point to the correct column, I'm going to use the OFFSET function to do that, OFFSET says “Hey, start over here in A2.” And OFFSET does lots of things, we're not going to use them all here.
How many rows to go down now, don't need to go any rows down.
How many columns to go over, oh yeah, we're going to use this one right here, the result of that MATCH.
How many rows tall we want, we want 3 rows tall, and how many columns wide, 1 column wide.
Now OFFSET is going to return all three cells in the column, which is not what we want to do.
So I'm going to copy those characters to the clipboard, everything except for the equal sign, we're going to use that here.
=MATCH, go find the x within, look up array, well that's where I paste in that OFFSET.
So it's going to return the values from that correct column and then, of course, an exact match, and what this does is it says “Hey, if you've chosen p3, x is in the second row.” Now we can test this out here, we choose p1, x is in the first row, choose p2, x is in the third row.
Alright, so then, value on the left-hand side, we’ll use the INDEX function, INDEX of what's over here, A2:A4, comma, which row we want, it's that row right there.
Alright, and so we get M3, there's our answer.
Now of course, I took all four of those pieces and put them back together to create this one really long formula up here, that does all of that in one formula.
So that's how I would solve it, Mike, will send over, you consult with Isaac, see what you guys come up with, be right back!
Mike: Thanks MrExcel!
Hey, Isaac said that he wants to use the INDEX function instead of OFFSET, so that's what we'll do, alright, I think of this kind of as a 3-way look up.
We have something here, we want a formula here, that's going to look here, find the position, then go down here, extract a whole column, that'll be the second look up.
First look up is finding the p-whatever, look up here, then using the x to then jump over to this third item, so there's like a 3-way look up.
First thing is, I'm just going to see if I can get the column number with the MATCH, just like MrExcel.
We take that, whatever is in that cell, look through there, ,0 , because it's an exact match, and that will give us 1.
If I change this to 2, then it gives me the 2nd column.
Now the next thing is, we need to somehow extract, right?
Now we need to extract this range of values, and then look in that range, find the x, which will deliver the row number to find the particular item over here.
MrExcel used the OFFSET, Isaac said you wanted to use the INDEX, so we'll try that.
INDEX, and the INDEX needs the array, got to do the whole array here, inside the table.
Now comma, and the whole trick here to get the index to extract a column, is to use row number 0.
Remember, we already have the column number right here from the match, but that 0 there means, in essence “Once you find the column, give me all the rows.” And that's what it does, it gives us all the rows at that particular column in that whole table there.
So now I’m going to close parenthesis, if I were to highlight this and hit the F9, key you can see it gives me exactly that range, I'm going to Ctrl+Z.
Now, that INDEX is going to be used inside of MATCH, because all it's doing is delivering the range, so I say MATCH.
And what do I want to look up? “x”, now I want x there, comma, the lookup array is that INDEX, and then I come to the end, and the match type is 0 because we're looking up an exact match.
So there it is, that delivers the 2, that's the row number that we then need to use in one more INDEX!
Because Remember, we're looking up here, so we have that whole MATCH INDEX MATCH thing, gives us the row number.
So now we just do INDEX of this right here, comma, that is the row number, we come to the end, close parentheses, BOOM, there it is, M2.
Alright, so we pick the p1 and we get that, we pick up p3, obviously this is a little data set, the big data set is what this was really going to be used for.
So there you go, we'll see you next trick!
Bill: Hey Mike, entertaining as always.
I want to thank everyone for stopping by, we'll, see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
