Forum Discussion
Excel formula for finding, ranking and returning nth value
Can anyone explain what formula or array formula I'd need for the following: Scenario - school cross country results Each school may have six runners but only the top four score. Column A lis...
Again - this is why I didn't want to upload the original document.
Use the document attached instead please.
rjtomkinson Provided that you are using Excel for 365 the basic formula for your example would be:
=TAKE(SORT(FILTER(A2:A45,B2:B45=E1)),4)
Though note the slight modifications to cells D1 and E1 where the latter now only contains the the school number, thus not "School 13".
Edit:
Added your file with two alternative formulas that should work in older Excel versions.