Adapting MAX function
 

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

Adapting MAX function
 

Try:

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

HTH

"nir020" wrote:

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

Adapting MAX function
 

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="Eng land",YOT!C$3:C$149)),C$3:C$150,0)

HTH
Kostis Vezerides

Adapting MAX function
 

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

"nir020" wrote:

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

Adapting MAX function
 

Still array entered...

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

--
Regards,
Dave


"nir020" wrote:

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