return MAX based on criteria

hi i have a spreadsheet like the following

A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan

and so on...

So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in.

Eg UK max revenue was in Feb of 25.

I am not sure how to apply the max formula with criteria. Is there any way to do this?

Thanks in advance!

Assuming your table, excluding headers, is in A2:C7

IF you Enter UK in F2, then to get Max, in G2 enter: =MAX(IF(A2:A7=F2,B2:B7))

to get Month where Max occurs: =INDEX(C2:C7,MATCH(1,(A2:A7=F2)*(B2:B7=G2),0))

where G2 contains first formula result.

Both formulas are array formulas and must be confirmed with CTRL+SHIFT+ENTER not just ENTER You will see { } brackets appear around them.

Hi,

May be something like this?

Originally Posted by oldchippy

Hi,

May be something like this?

When I select "France" from your drop down I still get "Feb" as month containing max when it should be "Jan"

Originally Posted by NBVC

When I select "France" from your drop down I still get "Feb" as month containing max when it should be "Jan"

Well spotted NBVC - there only room for one Eagle Eye

anoushka please change H4 to

=INDEX(Month,MATCH(1,(Country=F4)*(Revenue=G4),0))

Entered as Ctrl+Shift+Enter