Bill Jelen: Hey, I'm Bill Jelen from Mr.Excel.com and I've got a cool Excel tip for you today.
Mike Gel Girvin: Hey, this is Mike Gel Girvin at Excel is Fun and you too that I have a different way to do that.
Bill Jelen: Hey, welcome back it’s another Dueling Excel Podcast I'm Bill Jelen from Mr. Excel we’ll have Mike Gel Girvin from Excel is fun or even have Mike’s three year old son Isaac. Did you see his hilarious video at YouTube? Isaac actually put some cool tips to help there just randomly clicking around. Watch out like we’re going to have some competition there.
So anyway we have a question base in from Portugal. And what they have they have this metrics over here a little drop down or you can choose one of the column P1, P2, P3 and when we choose that we need to forgot where the access and then return the value over here on the left hand side. So I'm going to build this in a couple of steps let's talk about how I built it.
First thing I want to do is figure out when they choose P2 up there which column is that in so it will match. Go find this P2 within this range over here. D1 to D1 and we want the exact match but zero on SSR it were in column 2 and if we choose on the different P1 column 1 or P3 column 3. Now I want to point to the correct column I'm going to use the offset functions to do that offset. It says, hey, you start over here in A2 and offset does lots of thing we’re not going to use them all here.
How many rows to go down? No, 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 three rows tall and how many columns we will had, one column what. 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 accept for the equal sign. We’re going to use that here equal match go find the X within lookup array. Well, that’s where I paste in that offset so it’s going to return the values from that correct column and that of course an exact match.
And what this does is it says, “Hey, if you choose in P3 X is in the second row.” Now we can test this out here which is P1 X is in the first row, choose P2 X is in the third row, all right. So then, value on the left hand side we’ll use the index function. Index of what's over here A2 to A4 comma which row that we want. Oh, is that row right there, all right and so we get entries there's our answer.
Now of course I took all four of those pieces and put them back together to create as one, really long formula up here that does all of that in one formula. So that how I would solve it. Mike will center view and so with Isaac and see what you guys can come up with. I'll be right back.
Mike Gel Girvin: Thanks Mr. Excel. Hey, Isaac said that he wants to use the index function of offset so that’s we will do. All right, I think of this kind of as a three way lookup. We have something here and we want to formula here that’s going to look here find the position then go down here extract the whole column that would be the second lookup. First lookup that’s find in the P or whatever lookup here then use in the X to then jump over this third items so there's like thee way look up.
First thing is I'm just going to see if I can get the column number with the match just like Mr. Excel did we take that whatever so not cell look through there common zero because its an exact match and that will give us one if I change this to then it gives me the second 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 and Mr. Excel use the all set. Isaac anyone to use the index and we’ll try that. Index and the index needs to do array got to do the whole array here that inside the table.
Now comma and the whole trick here to get the index to extra a column is to use row number zero, remember we already have the column number right here from the match. But that zero there means in essence give once you’ve 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 where to highlight this and hit the F9 key you can see it gives me exactly that range. I'm going to control Z. Now that index is going to be use inside of match because all it’s doing is delivering the range so I said match and what do I want to look up in quotes X now I want to X there, comma the lookup array is that index and when it come to the end and in the match type is zero because we’re looking up on exact match. So there it is that delivers the two that’s the row number that we then need to use at 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 parenthesis, boom there it is M2. all right, so we pick the P1 and when get that we pick P3, obviously this is a little data set, the big data set as what this was really going to be use first. So there you go. We’ll see you next trick.
Bill Jelen: Hey Mike, entertaining is always. I want to thank everyone for stopping by we’ll see you next week for another Dueling Excel Podcast from Mr. Excel and Excel is Fun.
Transcription by:
Scribe4you Transcription Services