Lucy sends in a question. She has built a table of sales by rep by day. She used the MAX and MIN function to find the largest and smallest value. However, her boss then wants her to identify who had the largest and smallest value. This requires the use of INDEX, MATCH, and a couple of hidden columns. Episode 747 will show you how.
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. Welcome back to the MrExcel netcast. I'm Bill Jelen.
This is episode 747. 747 always makes me think of travel.
I just want to note that we have some great spring seminars coming up.
If you happen to be near one of these places, would love to have you come out and check out the half day Power Excel.
May 6th in Princeton, New Jersey, and then South Bend, Indiana, May 9th.
St. Louis on May 13th, and then, the next day, down the state in Springfield.
Springfield, Missouri.
Going back there.
There'll be a 3-day seminar with Mike Alexander in Dallas.
That's the 21st, 22nd, and 23rd of May.
Then, going to Trinidad.
Now, no matter where you are, wouldn't it be great to fly to Trinidad to come and see the Power Excel seminar?
Go ahead and try and sell that one to your boss.
Akron, Ohio on the 20th and then back to the Gleacher Center in Chicago on June 23rd.
For details on any of these, just go to MrExcel.
It's always near the top [ fold – 01:03 ] of the page.
See a list of upcoming seminars.
All the upcoming seminars are listed there and we’ve highlighted a few here.
So, please feel free to check that out.
Today's question is sent in by Lucy.
Lucy said, hey, I have a nice little spreadsheet here.
It shows all of the sales reps down the side, their sales by day going across, and I was able to figure out how to use the MAX formula or the MIN formula in order to figure out the largest or smallest, but then, Lucy said, my boss needs to see which person had the largest or smallest, and she was trying to use INDEX and all kinds of things like this, and I said, you know, hey, here's what we're going to do.
We're going to set up 2 new columns out here to the side where no one can see them.
So, we'll have =MIN of all of the sales here for ANDY, and then =MAX of all of the sales for ANDY, and copy those down, and, in reality, I told Lucy, you could put this out in column Z where no one will ever see it, and then what we're going to do is we're going to do a couple of things.
[ =MIN(B3:F3) ], [ =MAX(B3:F3) ] First of all, let's use the MATCH formula, the MATCH function.
We're going to say, hey, go find 960 within this column of largest values, , 0 -- that says we want an exact match -- and what it's going to say, it's going to say that that sale, $960, is on the 5th row.
1, 2, 3, 4, 5.
There it is on the 5th row.
So, that tells us that was sold by KAT.
[ =MATCH(C11,J3:J9,0) ] Well, now, to take that and actually change it to a name, we're going to wrap the INDEX function around the outside.
=INDEX, and we're going to index these names, , and basically we have to tell what row number we want.
Well, what the row number is the result of the match.
In this case, we can leave off the column number since there's only one column, we do need a ), and you'll see that that gives us KAT was the person with the largest.
[ =INDEX(A:3:A9,MATCH(C11,J3:J9,0)) ] Now, to do the MIN, we use =INDEX, again the same list of names.
This time though, which row do we want?
Well, we want the MATCH of the smallest number, so this 53 within this column over here, again a 0 to say that we want an exact match, ) twice.
So, we can see that KAT had the largest sale and MATT had the smallest sale.
[ =INDEX(A:3:A9,MATCH(C12,I3:I9,0)) ] Now, let’s just put in some new numbers here.
=RANDBETWEEN, let's say, 50 and 900.
Okay.
Now, this case, the largest sale was 891 and that was ANDY, so, sure enough, ANDY had the 891.
Smallest sale was 50 and that came from AMBER.
Sure enough, there it is.
[ =RANDBETWEEN(50,900) ] So, very flexible set of formulas.
The trick, though, is that we have to hide out here to the right-hand side, some columns, with MIN and MAX so that we were able to use the MATCH function to locate which person had that particular MIN or MAX.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
This is episode 747. 747 always makes me think of travel.
I just want to note that we have some great spring seminars coming up.
If you happen to be near one of these places, would love to have you come out and check out the half day Power Excel.
May 6th in Princeton, New Jersey, and then South Bend, Indiana, May 9th.
St. Louis on May 13th, and then, the next day, down the state in Springfield.
Springfield, Missouri.
Going back there.
There'll be a 3-day seminar with Mike Alexander in Dallas.
That's the 21st, 22nd, and 23rd of May.
Then, going to Trinidad.
Now, no matter where you are, wouldn't it be great to fly to Trinidad to come and see the Power Excel seminar?
Go ahead and try and sell that one to your boss.
Akron, Ohio on the 20th and then back to the Gleacher Center in Chicago on June 23rd.
For details on any of these, just go to MrExcel.
It's always near the top [ fold – 01:03 ] of the page.
See a list of upcoming seminars.
All the upcoming seminars are listed there and we’ve highlighted a few here.
So, please feel free to check that out.
Today's question is sent in by Lucy.
Lucy said, hey, I have a nice little spreadsheet here.
It shows all of the sales reps down the side, their sales by day going across, and I was able to figure out how to use the MAX formula or the MIN formula in order to figure out the largest or smallest, but then, Lucy said, my boss needs to see which person had the largest or smallest, and she was trying to use INDEX and all kinds of things like this, and I said, you know, hey, here's what we're going to do.
We're going to set up 2 new columns out here to the side where no one can see them.
So, we'll have =MIN of all of the sales here for ANDY, and then =MAX of all of the sales for ANDY, and copy those down, and, in reality, I told Lucy, you could put this out in column Z where no one will ever see it, and then what we're going to do is we're going to do a couple of things.
[ =MIN(B3:F3) ], [ =MAX(B3:F3) ] First of all, let's use the MATCH formula, the MATCH function.
We're going to say, hey, go find 960 within this column of largest values, , 0 -- that says we want an exact match -- and what it's going to say, it's going to say that that sale, $960, is on the 5th row.
1, 2, 3, 4, 5.
There it is on the 5th row.
So, that tells us that was sold by KAT.
[ =MATCH(C11,J3:J9,0) ] Well, now, to take that and actually change it to a name, we're going to wrap the INDEX function around the outside.
=INDEX, and we're going to index these names, , and basically we have to tell what row number we want.
Well, what the row number is the result of the match.
In this case, we can leave off the column number since there's only one column, we do need a ), and you'll see that that gives us KAT was the person with the largest.
[ =INDEX(A:3:A9,MATCH(C11,J3:J9,0)) ] Now, to do the MIN, we use =INDEX, again the same list of names.
This time though, which row do we want?
Well, we want the MATCH of the smallest number, so this 53 within this column over here, again a 0 to say that we want an exact match, ) twice.
So, we can see that KAT had the largest sale and MATT had the smallest sale.
[ =INDEX(A:3:A9,MATCH(C12,I3:I9,0)) ] Now, let’s just put in some new numbers here.
=RANDBETWEEN, let's say, 50 and 900.
Okay.
Now, this case, the largest sale was 891 and that was ANDY, so, sure enough, ANDY had the 891.
Smallest sale was 50 and that came from AMBER.
Sure enough, there it is.
[ =RANDBETWEEN(50,900) ] So, very flexible set of formulas.
The trick, though, is that we have to hide out here to the right-hand side, some columns, with MIN and MAX so that we were able to use the MATCH function to locate which person had that particular MIN or MAX.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
