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

Try:

=INDEX($A$3:$A$150,MATCH(MAX(IF($B$3:$B$150="England",$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

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

..... 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

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))

--
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