MAX and perhaps a VLOOKUP?

**JoeyGirl** · 01-10-2010, 09:41 PM

Hi Guys,

I hope what I am trying to do is possible... I would be eternally greatful for any help!

I have a spreadsheet that is updated every month (100+ Rows, 38 Columns) and what I need to do is list the top sales person by 'dollars above target' in each of our 6 product classes. The staff names are in column A and the comparison to targets for the first product is in column AA.

My best idea so far is getting the MAX of the Sales vs Budget and applying a conditional format to highlight the data equal to the figure in that cell then finding the name myself from the list in Column A, but I was hoping someone might know a way to have Excel generate this name for me? - Also, is there a formula I can use that will give me the top 3 scores for each product?

Thanks in advance

**teylyn** · 01-10-2010, 09:43 PM

Hi JoeyGirl, welcome to the forum. It'll be hard to write formulas without knowing more about the data layout. Can you put together a few dozen rows of sample data, make sure they don't contain confidential data and then post the workbook up here?

You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

cheers

**JoeyGirl** · 01-10-2010, 10:08 PM

Hi,

Thanks for responding so quickly.

I've attached a sample bit of data and removed any identifying anything. Everything in the columns beyond the comparison is just sales payment formula, and I cut out some HR data at the start but added in 4 hidden columns to move the comparison back to Column AA for the ease of anyone playing at home.

Cheers

**teylyn** · 01-10-2010, 10:41 PM

Hi,

maybe something like this, starting in column AH

Code:

Top 3					
Product 1	Product 2	Product 3	Product 4	Product 5	Product 6
Operator 2	Operator 6	Operator 6	Operator 3	Operator 1	Operator 5
Operator 6	Operator 2	Operator 1	Operator 2	Operator 2	0
Operator 5	Operator 1	Operator 3	Operator 1	Operator 6	Operator 1

AH3 =INDEX($A$1:$A$11,MATCH(LARGE(AA$3:AA$11,1),AA$3:AA$11,0))
AH4 =INDEX($A$1:$A$11,MATCH(LARGE(AA$3:AA$11,2),AA$3:AA$11,0))
AH5 =INDEX($A$1:$A$11,MATCH(LARGE(AA$3:AA$11,3),AA$3:AA$11,0))

Copy these across. Mind the placement of the $ signs.

You can also total up comparison results per row and calculate an overall top 3

**JoeyGirl** · 01-10-2010, 10:59 PM

Hey,

I've put in your formula and you're definitely on the right track! This is exactly the type of thing I was hoping to do.. yay! :D

Just one little problem tho.. the results it gives aren't right - for example Product 1 says that Operator 2 has the highest result, where Operator 2 actually made no sales at all that month and should actually be equal last with Operator 6 (who came second in the formula).

Im guessing this would just be a small tweak to the formula?

**teylyn** · 01-10-2010, 11:21 PM

Fatal error in the formula. Programmer shot.

In all three formulas, change

=INDEX($A$1:$A$11....

into

=INDEX($A$3:$A$11....

**JoeyGirl** · 01-10-2010, 11:36 PM

Aha - Perfect!

I've added it into my workbook and it's spot on! Thats one less fiddly little thing for me to do every month

Thanks so much for your help Teylyn, you've made my day!

Should I close this thread now? How do I do that?

**teylyn** · 01-10-2010, 11:39 PM

I like making days ...

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save