I'm also creating a list according to the reps sales in order. I've changed
the max function to the large function to show the 2nd, 3rd, 4th largest
numbers in the list. But some of them are duplicates (ie. sales of 10 show up
more than once). The output is showing one of the dealers multiple times
because this is the first one in the list with the designated sales value. Is
there any way to change the function so that it will list out different sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and vice
versa. Your other dates are in mmm-yy format but what DAY are they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me with.

Using the same data set below, if I wanted to find the dealer that was
associated with the max found in the expression below, how would I do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed values.

What did you try that didn't work? What result did you get? What did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?