Excel formula for finding, ranking and returning nth value

[rjtomkinson]

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 lists finishing position ascending from 1.

Column B lists the school number worn by each runner (all runners from a school wear the same number) 

I would like a formula which finds, from Column B, a specific school number, identifies the relative nth position for that school, and returns the overall finishing position from Column A. 

e.g. Average School (number 15) students finished 6th, 10th, 12th, 21st, 28th and 30th. 

Formula function:

Find 15 in Column B, identify 2nd lowest = 10.

Thank you! 

[#2]

@rjtomkinson 

I'm sure there are folks here--including me--who'd like to help. You could help us help you by providing a worksheet. It sounds like you must have one; if not, you could readily create a more complete example than the verbal example you've given (just by entering into Excel the actual results from whatever paper tally sheet you've been using heretofore).

Put that sample worksheet on OneDrive or GoogleDrive and paste a link here that grants access to it.

In the process, please describe just a bit more fully this formula (or the overall outcome) that you're seeking.

I found what you have written to be a bit confusing and we need to be really clear in order to write any formulas.

Here are what I saw as ambiguous (or needing more clarification) in your description.

Each school may have six runners but only the top four score. Top four from each school, right?

Column A lists finishing position ascending from 1.    Clear

Column B lists the school number worn by each runner (all runners from a school wear the same number)    Clear

I would like a formula which finds, from Column B, a specific school number, identifies the relative nth position for that school, and returns the overall finishing position from Column A. See below

e.g. Average School (number 15) students finished 6th, 10th, 12th, 21st, 28th and 30th. Clear enough

Formula function:

Find 15 in Column B, Clear enough

identify 2nd lowest = 10.  Second lowest? Where did that "Second" come from in the first place? I see it could an arbitrary nth, but that's not clear.

Do we need to identify 1st, 2nd, 3rd and 4th for each school?

If not, what connection does "2nd lowest" have with "only the top 4 score"?

How does any of it connect with calculating the "overall finishing position" and some kind of overall score for each school?

I'm sure it's all clear in your mind, and maybe it would be to anybody else who's done this kind of scoring and ranking....but it seems to me that you're taking some of the process for granted (unless I'm just missing something very obvious).

[#3]

@mathetes 

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.

[#4]

@rjtomkinson 

I did not upload the file initially because I would have been breaching data protection law.

Nope, so long as no names of actual people are involved--and they're not--you're fine.

But I'm on vacation--just checking in--and even if that weren't the case, I doubt I'd have the most efficient solution. So I'm going to invite a few others -- Sergei Baklan  Hans Vogelaar Riny_van_Eekelen          NikolinoDE  Peter Bartholomew   -- to chime in. I'm confident that at least one of them will have your solution. 

[#5]

Yes - I need the 1st, 2nd, 3rd and 4th position runners 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.

[#6]

@peiyezhu 

Again - this is why I didn't want to upload the original document.

Use the document attached instead please.

[#7]

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

[#8]

@Riny_van_Eekelen 

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.

[#9]

@rjtomkinson So you are using the formula surrounded by the curly brackets in I2? That's an old style array formula that needs to be confirmed with Ctrl-Shift-Enter (CSE), i.e. not just Enter. As soon as you touch it you get that error message. You need to select all four cells, enter the formula  and then CSE. Then you can select those four cells again and drag them across. It's actually quite annoying to work with such CSE type formulas. By the way, you need to make the references to columns A and B absolute for copy by dragging to work correctly. Done that in the attached file.

I've also include a traditional solution that avoids CSE. I would use that one if you really can't get your hands on a modern Excel version.

[#10]

@Riny_van_Eekelen 

Thank you for your efforts.

Without understanding the language of the functions and formulas I think I am destined to fail - when using your formula I either get a #ref or #name error.

Additionally, I want to transfer this to Google Sheets which my school uses, but it doesn't recognise the aggregate function.

Unfortunately I am no closer to a solution.

Thanks again

[#11]

@rjtomkinson Sorry to hear. I know nothing about GS, so I can't help you any further.

[#12]

 Have you tried Excel App on Android smart phone?

@Riny_van_Eekelen's file works fine on my side.

Re:

when using your formula I either get a #ref or #name error

Can you share the workbook with error formular?

enter the error formular like '=myErroFumular(range)

Additionly,you need list 4 runners in each sheets rather than put them(all 4 runners) in one sheet?

@rjtomkinson