Adapting MAX function

[Guest]

I have created a forumla in excel which selects the the highest value in a
range based on certain criteria contained in a neighbouring cell, it is shown
below:-

=MAX(IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149)))

Can this formula be adapted so it does not pick the highest value, but would
pick up the row heading of the row with the highest value, in this example it
would be the value held in cell A

Thanks

 

[Guest]

Try:

=INDEX($A$3:$A$150,MATCH(MAX(IF($B$3:$B$150="England",$C$3:$C$150)),$C$3:$C$150,0))

HTH

 

[vezerid]

Assuming that the conditional MAX is unique for England and that it is
not the same as another country's, the following formula should produce
the header:

=INDEX(A$3:A$150,MATCH(MAX(IF(YOT!$B$3:$B$150="England",YOT!C$3:C$149)),C$3:C$150,0)

HTH
Kostis Vezerides

 

[Guest]

..... Enter with Control+Shift+enter

 

[Guest]

Still array entered...

=INDEX(YOT!$A$3:$A$150,MATCH(MAX(IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149))),IF(YOT!$B$3:$B$150="England",(YOT!C$3:C$149)),0))