Best formula for required result

[Marvo]

Hi, I'm looking for the best formula to achieve the required result.

I've made a simple mock up. What I want is a formula in D11 to find the highest score of handicappers 18 and under. Then in D12 the highest score of handicappers 19+. If possible if there are more than one winner in each category, I'd like a return of zero or "".

Many thanks.

 

[etaf]

how about
=IF(COUNTIFS(C2:C8,MINIFS(C2:C8,C2:C8,"<="&18))>1,0,INDEX(B2:B8,MATCH(MINIFS(C2:C8,C2:C8,"<="&18),C2:C8,0)))
and
=IF(COUNTIFS(C2:C8,MAXIFS(C2:C8,C2:C8,">"&18))>1,0,INDEX(B2:B8,MATCH(MAXIFS(C2:C8,C2:C8,">"&18),C2:C8,0)))

Book2
	A	B	C	D
1
2		Jim	6	4
3		Dave	13	3
4		Jack	22	2
5		terry	18	3
6		bob	36	4
7		frank	27	2
8		john	17	3
9
10		18 & Below		Jim
11		above 18		bob
Sheet1

Cell Formulas
Range	Formula
D10	D10	=IF(COUNTIFS(C2:C8,MINIFS(C2:C8,C2:C8,"<="&18))>1,0,INDEX(B2:B8,MATCH(MINIFS(C2:C8,C2:C8,"<="&18),C2:C8,0)))
D11	D11	=IF(COUNTIFS(C2:C8,MAXIFS(C2:C8,C2:C8,">"&18))>1,0,INDEX(B2:B8,MATCH(MAXIFS(C2:C8,C2:C8,">"&18),C2:C8,0)))

 

[Fluff]

Another option

=LET(f,SORT(FILTER(B2:D10,C2:C10<=18),3,-1),p,INDEX(f,,3),IF(LARGE(p,1)=LARGE(p,2),"",INDEX(f,1,1)))

and

=LET(f,SORT(FILTER(B2:D10,C2:C10>18),3,-1),p,INDEX(f,,3),IF(LARGE(p,1)=LARGE(p,2),"",INDEX(f,1,1)))

 

[Marvo]

I'm struggling with both guys when I move them over to the main workbook.

In etaf's solution, where in the formula does it refer to column D (the points score)?
When the formula is entered to the workbook it produces an incorrect result but there is no data in the points column so I don't know why it should?

In Fluffs solution, the actual rows being used are B, C & E so I don't know how to change the B2:D10 to B2:E2 without it including C.

Would it help if I gave the actual columns and rows with cell numbers?

The players names are in B4:B61. The handicaps are in C4:C61. The points scores are in E4:E61.

 

[etaf]

i dont , sorry , my mistake -

yes , real example of the data and how setout will help a lot , rather than a mock up thats not the same formatting/layout -

i'll leave with fluff for now, as i was just working on his solution to see how it worked and noticed the D column and my mis-reading the requirement

 

[Fluff]

How about

=LET(f,SORT(FILTER(B4:E61,C4:C61<=18),4,-1),p,INDEX(f,,4),IF(LARGE(p,1)=LARGE(p,2),"",INDEX(f,1,1)))

 

[Marvo]

It's coming back with a #NUM! error.

This is my actual formula. The data is on a different sheet, is that where the problem lies?

=LET(f,SORT(FILTER(' Master'!$B$4:$E$61,' Master'!$C$4:$C$61<=18),4,-1),p,INDEX(f,,4),IF(LARGE(p,1)=LARGE(p,2),"",INDEX(f,1,1)))

I'm sorry I don't know what the f and the p refer to.

 

[Fluff]

Do you have at least two players with a handicap of <=18?

 

[Marvo]

Yes, there are loads. at least 35.

 

[Fluff]

In that case do you have any #num errors in any of those ranges?