Forum Discussion
Excel formula for finding, ranking and returning nth value
I did not upload the file initially because I would have been breaching data protection law.
Additionally the layout of the document is perhaps not the most intuitive, and I think it requires even more explanation.
Each year group (race category) has a different tab.
The columns on the left are as described - finishing position and school number.
The other table lists the finishing position of the top four runners for each school. I would like a formula which finds that data rather than me manually going through the finishing position data and entering that in to the table.
Yes - I need the 1st, 2nd, 3rd and 4th position runners for each school.
e.g. if School A runners finished 4th, 8th, 19th and 32nd
The 2nd runner (or second lowest number because surely that's the language that the formula would use - Excel does not have that context or meaning) is 8.
As you can see from the table, once I have those positions I can sum and rank the totals for each school.
Which tabel is your expected result according to those raw datas?
Which tables are raw datas?
There are many worksheets and each sheet also includes 3 seperate range/tables.
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.
Thank you for this.
How can I extrapolate this formula? I get the alert that I cannot edit an array formula.
I need to be able to copy it across for multiple schools
e.g. School: 13, 18, 41 etc
each time searching the same table for the 1st to 4th runners for that respective school number.
