Bdtyktl

I know this has been asked before, but I'm not able to make sense of other people's examples.

Here is a formula I have that works:

=INDEX('[AL DOE.xlsx]DOE'!$E$2:$E$5000,MATCH(LARGE('[AL DOE.xlsx]DOE'!$L$2:$L$5000,1),'[AL DOE.xlsx]DOE'!$L$2:$L$5000,0))

It looks at the AL DOE sheet and returns the school name (column E) that has the highest percentage of members (column L).

Now I want to have it look for the highest percentage of members among primary schools. This info is in column P.

This is what I tried:

=INDEX('[AL DOE.xlsx]DOE'!$E$2:$E$5000,MATCH(LARGE('[AL DOE.xlsx]DOE'!$L$2:$L$5000,1),('[AL DOE.xlsx]DOE'!$L$2:$L$5000) * ('[AL DOE.xlsx]DOE'!Primary=$P$2:$P$5000),0))

It gives me a #N/A error. Here is an example of the data:

-------E-------------------------------L-----------------------P

------School--------------------Percent Members----------School Level

John E. Bryan Elem Sch PTA------------84%------------------Primary

Goldsmith Schiffman Elementary PTA----21%------------------Primary

Huntsville Hi Sch PTSA----------------96%--------------------High 

Bernice Causey Middle PTSA------------54%-------------------Middle

Hampton Cove Elem PTA-----------------34%------------------Primary

Elsie Collier Elem PTA----------------78%------------------Primary

There appears to be a few different bits going wrong on your new formula but you've almost got it.

The first bit is the LARGE formula; currently it will still be returning 96% as there is nothing on that restricts this function to Primary only. This bit should be something like

LARGE(IF('[AL DOE.xlsx]DOE'!$P$2:$P$5000' = "Primary", [AL DOE.xlsx]DOE'!$L$2:$L$5000),1)

This says to looks for the largest value in L but only in the rows where P is "Primary". This would now return 84% and feed into your MATCH part of the query but there's an issue there. At he endo fo your formula you currently have:

('[AL DOE.xlsx]DOE'!Primary=$P$2:$P$5000)

when it should be

('[AL DOE.xlsx]DOE'!$P$2:$P$5000 = "Primary")

So this would give you a final formula of

=INDEX('[AL DOE.xlsx]DOE'!$E$2:$E$5000,MATCH(LARGE(IF('[AL DOE.xlsx]DOE'!$P$2:$P$5000 = "Primary", '[AL DOE.xlsx]DOE'!$L$2:$L$5000),1), ('[AL DOE.xlsx]DOE'!$L$2:$L$5000) * ('[AL DOE.xlsx]DOE'!$P$2:$P$5000 = "Primary"),0))

You also needs this to be an array formula so after typing it in you'll need to press Ctrl, Shift & Enter together