It's Duel 100 with Mike "ExcelisFun" Girvin and Bill "MrExcel" Jelen! Revisting the topic of their first-ever Dueling Podcast, Mike and Bill up the ante and make the task increasingly difficult in theory...but they manage to pull it off without a hitch.
Transcript of the video:
Hey, welcome back it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'm here with Mike Girvin from Excel Is Fun.
This is episode 100.
Mike: Amazing!
Bill: and you believe as Mike pulled out the trick that we did for episode 1 only made it like a hundred times hard.
And so, we'll see how we go you're on, no here's the goal, here's the goal we're doing a two-way look up at this time, this time it's not an exact match it's an approximate match down the left-hand side and because four or more five, six, seven, eight has to pick up this four.
It has to be an approximate match across the top and then he says, "Do conditional formatting to highlight the one that matches".
I'm like you're crazy. I can, I can, I can do the formula.
So, the formula, the formula is going to have three functions and it's going to be an INDEX and MATCH and a MATCH I just that.
I want to do the match first. So, the first match says, we're going to look for this quantity along this scalar here, I'll press-- Do I have to press F4?
Not really, but I'm going to anyway comma and I want the 1, which is like the true version of the VLOOKUP.
and that's going to tell me that 42 should be using the third row.
So, that is we don't qualify for the 49 price break, we do qualify for the 25 price break.
That's really, really cool.
Now, over here equal MATCH.
Match is cool, it goes both horizontal and vertical we have these up here. I'll press F4 again comma 1 and to do the little test we-- There we go.
All right! excellent so now, I have these two little bits.
I'm going to take those two bits.
I'm going to put in together inside of an INDEX function.
So, the INDEX function says, hey here's all of our answers. I'll press F4, which row do I want, I want the answer from that, which column do I want, I want the answer from that.
Now, that's three formulas it look like Mike wanted that put in one spot.
So, my favorite trick copy everything except for the equal sign come back here and choose B16, Control+V, and then everything, but the equal sign Control+C, come back here.
Mike: That is some keyboard magic trick.
Bill: Here we go, Control+V, that is a long, foreign people look at that saying, hey, you must be MrExcel that whole thing, Control+X over here, equal Control+V, 2 equal signs there we go and then he wants a total I guess I can do that.
All right! So, there's my answer. It's really a conditional format.
I don't know, I want to watch Mike and see what he does.
Mike: All right! Thanks MrExcel.
100 amazing, well, okay!
I'm going to just do the straight first part of this as VLOOKUP and then for the column INDEX, I'll use MATCH.
I'm gonna say equals VLOOKUP, the lookup value I'm looking up something here in the first column.
So, I'm going to look up that comma within this whole table right here, this VLOOKUP looking the value up here will give me the row right, comma and then I need a column number well i'm going to use MATCH.
I'm going to look up this comma.
Now, notice if I highlight this, it'll give me one number two few.
So, I'm going to highlight all of these and I'm it is approximate match, the default if you leave it off here is one.
So, I'm going to leave it off, so that should give me my two-way look up 25 3 that should give me 223.
Now, the total equals this times 25, this is a quantity of shirts and colors, right?
So, 80 75. Now to do conditional formatting, I'm going to do it an actual.
So, here it's this row and this one.
I'm going to do a color this way a color this way and then an intersecting color.
I'm going to highlight the whole table here and I have to match, I have to find something here and match it against this.
So, I'm going to with the active cell in the upper corner Alt+O+D, Alt+N arrow arrow arrow tab.
Now, for an entire row I need to match the row header.
So, I'm going to click here you always build your formula from the point of view of that active cell and I'm going to lock it just the column reference, but not the row and I'm saying anytime that's equal to.
Now, I could use VLOOKUP, but I'm going to use look up which is the same it does approximate match.
This is what we're doing here, I'm going to say look up, look up the quantity and that's F4 comma and then I'm going to highlight this entire column here just the single column.
Now, it's kind of weird, right?
I'm looking up this 25 to try and find you know, some value here, but that'll work that'll give me the entire row format it.
At least, I got my fingers crossed it's yellow, yellow, yellow.
Okay so, there it looks like it's work, Alt+O+D, New Rule.
So, equals the row header this one's got to be locked with the, the row reference.
Anytime that's equal to look up of this one comma within this and it's by default approximate match, close parenthesis, format this I'm going to say orange click OK, click OK, click OK.
Alt+O+D, now i need to put both of these.
Now, I'm going to cheat here because I can't type, I'm going to copy that an edit mode, click OK, New Rule because both of them have to be true. I'm gonna say and, and then Control+V.
I need one more, but I'm going to close parenthesis just close it and then come down here and steal this.
So, I don't have to type it Control+C, OK and then come here in comma Control+V I got my fingers crossed, red and a font color white.
There we go. So let's try it. I ordered 2700 shirts and I want five colors.
So, there we go.
All right, throw back to MrExcel. There you go to good one here.
Bill: that's insane that's insane, I'm going to watch this one 10 times just a figure what the heck you did.
All right, hang on I'm gonna, I'm you know, sometimes Mike sometimes, sometimes highlight cell rules I want to highlight duplicate values look at that there are no duplicates in this bad boy, if there had been a duplicate, I'd go to the pricing manager and having changes is that way there were no duplicates and what we do is Alt+O+D and we say we want to actually delete that rule and we are going to do New Rule and we're a format only cells that contain cell value is equal to.
Hey, man already did the hard stuff down here I just want to be equal that and fill it's the red one click OK, click OK and we're done.
Mike: Look at that.
Bill: And 5000 shirts, there we go.
Sweet, easy.
Mike: That's the point, number one-- Bill: No, no, no they're gonna like yours better, they always like yours better.
I've learned that after 100 episodes.
Well, hey, that was episode 100 of the dueling Excel podcast with any luck, we'll be back next week for episode 101.
Wanna thank you for stopping by.
See you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
I'm Bill Jelen from MrExcel, I'm here with Mike Girvin from Excel Is Fun.
This is episode 100.
Mike: Amazing!
Bill: and you believe as Mike pulled out the trick that we did for episode 1 only made it like a hundred times hard.
And so, we'll see how we go you're on, no here's the goal, here's the goal we're doing a two-way look up at this time, this time it's not an exact match it's an approximate match down the left-hand side and because four or more five, six, seven, eight has to pick up this four.
It has to be an approximate match across the top and then he says, "Do conditional formatting to highlight the one that matches".
I'm like you're crazy. I can, I can, I can do the formula.
So, the formula, the formula is going to have three functions and it's going to be an INDEX and MATCH and a MATCH I just that.
I want to do the match first. So, the first match says, we're going to look for this quantity along this scalar here, I'll press-- Do I have to press F4?
Not really, but I'm going to anyway comma and I want the 1, which is like the true version of the VLOOKUP.
and that's going to tell me that 42 should be using the third row.
So, that is we don't qualify for the 49 price break, we do qualify for the 25 price break.
That's really, really cool.
Now, over here equal MATCH.
Match is cool, it goes both horizontal and vertical we have these up here. I'll press F4 again comma 1 and to do the little test we-- There we go.
All right! excellent so now, I have these two little bits.
I'm going to take those two bits.
I'm going to put in together inside of an INDEX function.
So, the INDEX function says, hey here's all of our answers. I'll press F4, which row do I want, I want the answer from that, which column do I want, I want the answer from that.
Now, that's three formulas it look like Mike wanted that put in one spot.
So, my favorite trick copy everything except for the equal sign come back here and choose B16, Control+V, and then everything, but the equal sign Control+C, come back here.
Mike: That is some keyboard magic trick.
Bill: Here we go, Control+V, that is a long, foreign people look at that saying, hey, you must be MrExcel that whole thing, Control+X over here, equal Control+V, 2 equal signs there we go and then he wants a total I guess I can do that.
All right! So, there's my answer. It's really a conditional format.
I don't know, I want to watch Mike and see what he does.
Mike: All right! Thanks MrExcel.
100 amazing, well, okay!
I'm going to just do the straight first part of this as VLOOKUP and then for the column INDEX, I'll use MATCH.
I'm gonna say equals VLOOKUP, the lookup value I'm looking up something here in the first column.
So, I'm going to look up that comma within this whole table right here, this VLOOKUP looking the value up here will give me the row right, comma and then I need a column number well i'm going to use MATCH.
I'm going to look up this comma.
Now, notice if I highlight this, it'll give me one number two few.
So, I'm going to highlight all of these and I'm it is approximate match, the default if you leave it off here is one.
So, I'm going to leave it off, so that should give me my two-way look up 25 3 that should give me 223.
Now, the total equals this times 25, this is a quantity of shirts and colors, right?
So, 80 75. Now to do conditional formatting, I'm going to do it an actual.
So, here it's this row and this one.
I'm going to do a color this way a color this way and then an intersecting color.
I'm going to highlight the whole table here and I have to match, I have to find something here and match it against this.
So, I'm going to with the active cell in the upper corner Alt+O+D, Alt+N arrow arrow arrow tab.
Now, for an entire row I need to match the row header.
So, I'm going to click here you always build your formula from the point of view of that active cell and I'm going to lock it just the column reference, but not the row and I'm saying anytime that's equal to.
Now, I could use VLOOKUP, but I'm going to use look up which is the same it does approximate match.
This is what we're doing here, I'm going to say look up, look up the quantity and that's F4 comma and then I'm going to highlight this entire column here just the single column.
Now, it's kind of weird, right?
I'm looking up this 25 to try and find you know, some value here, but that'll work that'll give me the entire row format it.
At least, I got my fingers crossed it's yellow, yellow, yellow.
Okay so, there it looks like it's work, Alt+O+D, New Rule.
So, equals the row header this one's got to be locked with the, the row reference.
Anytime that's equal to look up of this one comma within this and it's by default approximate match, close parenthesis, format this I'm going to say orange click OK, click OK, click OK.
Alt+O+D, now i need to put both of these.
Now, I'm going to cheat here because I can't type, I'm going to copy that an edit mode, click OK, New Rule because both of them have to be true. I'm gonna say and, and then Control+V.
I need one more, but I'm going to close parenthesis just close it and then come down here and steal this.
So, I don't have to type it Control+C, OK and then come here in comma Control+V I got my fingers crossed, red and a font color white.
There we go. So let's try it. I ordered 2700 shirts and I want five colors.
So, there we go.
All right, throw back to MrExcel. There you go to good one here.
Bill: that's insane that's insane, I'm going to watch this one 10 times just a figure what the heck you did.
All right, hang on I'm gonna, I'm you know, sometimes Mike sometimes, sometimes highlight cell rules I want to highlight duplicate values look at that there are no duplicates in this bad boy, if there had been a duplicate, I'd go to the pricing manager and having changes is that way there were no duplicates and what we do is Alt+O+D and we say we want to actually delete that rule and we are going to do New Rule and we're a format only cells that contain cell value is equal to.
Hey, man already did the hard stuff down here I just want to be equal that and fill it's the red one click OK, click OK and we're done.
Mike: Look at that.
Bill: And 5000 shirts, there we go.
Sweet, easy.
Mike: That's the point, number one-- Bill: No, no, no they're gonna like yours better, they always like yours better.
I've learned that after 100 episodes.
Well, hey, that was episode 100 of the dueling Excel podcast with any luck, we'll be back next week for episode 101.
Wanna thank you for stopping by.
See you next time for another dueling Excel podcast from MrExcel and Excel Is Fun.
