Problems combining IF and MAX to get the right result

Hi, I was wondering if anyone could help. Its a tricky question to ask - so I am hoping the answer is more straightforward than the wording of the question! OK Imagine the following table

Fruit Date Price New Date
Apple 01/01/00 1
Apple 02/02/00 2
Banana 03/03/00 1
Banana 04/04/00 2
Pear 05/05/00 1
Pear 06/06/00 2

WHat I would like to appear in the New date column is the date that each fruit was at its most expensive, so the table would become:

Fruit Date Price New Date
Apple 01/01/00 1 02/02/00
Apple 02/02/00 2 02/02/00
Banana 03/03/00 1 04/04/00
Banana 04/04/00 2 04/04/00
Pear 05/05/00 1 06/06/00
Pear 06/06/00 2 06/06/00

Is there a formula that would provide me with the outcome above.

Many thanks for your help,

Paul

See if this helps:

http://www.dailydoseofexcel.com/arch...ple-variables/


Regards, TMS

Hi Paul and welcome to the forum.

Will be easier for us to help you, if..

1) Do, what the Moderator, asked for you

2) Upload a small sample workbook.

=INDEX($B$2:$B$10,MATCH(MAX(IF($A$2:$A$10=A2,$C$2:$C$10)),$C$2:$C$10,0))

Committed with Ctrl-Shift-Enter rather than just Enter (Array Entered)

{=INDEX($B$2:$B$10,MATCH(MAX(IF($A$2:$A$10=A2,$C$2:$C$10)),$C$2:$C$10,0))}


Regards, TMS

Maybe to combine what TMS, suggested(as helper column) and an INDEX&MATCH function.

So in E2 and copy down, use this Array foemula(C+S+E)

=MAX(IF($A$2:$A$10=A2,$C$2:$C$10))

Then in D2 and copy down, this.

INDEX($B$2:$B$10,MATCH(E2,$C$2:$C$10,0))

PHere is also Pivot table solution...
And formula...

@Fotis: is that kind of what I said in post #7 but all together?

@TMS

It's better one, than two!

@zbor: neat, I'd never have thought of using a Pivot Table. Suggest you lose the grand total though. Cheers, TMS

That's cosmetic

@TMS

Most important for me is to see you here with us, to suggest solutions, again.

This forum, needs people like you!

@Paul: you're welcome.

@Fotis: I've not been anywhere ... apart from the holiday in Canada (which was very good)

@zbor: you want it to be pretty though, don't you ... not sure about the lippy and mascara

Originally Posted by TMShucks

=INDEX($B$2:$B$10,MATCH(MAX(IF($A$2:$A$10=A2,$C$2:$C$10)),$C$2:$C$10,0))

This works for the example given but may not work if the max value is the same for different fruit, e.g. if C5 is changed to 9 you get incorrect results for Pear.

You need a formula that will check that both the max value and the fruit are the same, i.e. this array formula for example

=INDEX(B$2:B$10,MATCH(1,(MAX(IF(A$2:A$10=A2,C$2:C$10))=C$2:C$10)*(A$2:A$10=A2),0))

[edit: or the formula suggested by zbor in attachment!]

@DLL: thank you