Finding Nth ranking value based on multiple criteria

This one has got me stuck. I'm trying to score multiple dual track meets while 3 teams compete simultaneously. In the scoring the 1st and 2nd place positions for each dual meet must come from the 1st heat only. 3rd thru 6th place can come from any of the heats. I realize that there will probably be a different formula for 1st/2nd and 3rd-6th.

I've attached a small sample worksheet with the desired results highlighted in yellow with an explanation of the results below the highlighted cells.

Thanks for the come back rylo. Reading back on my comments I realize they sound confusing. 1st and 2nd must come from Heat 1 and would be the lowest times in that heat. So 3rd - 6th would be the numerically lowest times excluding the 1st and 2nd places. As in the two examples you mentioned, where 3rd place is actually the lowest time.

I've adjusted the notes and re-attached the example.

Thanks

I had thought this was possible with formulas. I sit something that needs a VBA solution for instead???

Bump no response

Even that don't agree with all your manually putted results, take a look to this, pls..

Thanks Fotis I appreciate the comeback, however the restriction for schools in B2:B21 is not handled and it doesn't look like there is anyway of allowing place #3-#6 to be from any heat number. I also am having a hard time visualizing what $G$2:$G$21 is referring to as it has no way of helping to determine the outcomes.

I am quite sure that there is something that i can not understand..

the restriction for schools in B2:B21 AND which of these have 1 in column E(Heat#) is handled in hidden column H. This finds which of the schools in column b are Kennett OR Sun Valley AND have 1 in Column E..

As you say in your sample sheet..

.

..1st above is lowest Converted Time (F2:F21)
where school in (B2:B21) is either
"Sun Valley" or "Kennett" AND
the Heat# in E2:E21 is "1"

Then i believe that formulas in column J, do this.

What am i missing?

I can see now that the helper coloumn and the formula use Kennett OR Sun Valley based on a value of "1" in H2:H21, And I think that would be fine for 1st and 2nd place. 3rd-6th need the same school distinction and they should be the times from small to large EXCLUDING the times for 1st and 2nd AND can be from any heat# I hope that helps to clarify it a bit...

...I hope that helps to clarify it a bit...

I think Yes. But as time here is 22:20, i don't have a clear thought. I'd like to believe that Tomorrow morning we'll find the correct solution for this.

Morning guys

Thanks for both of your followups. Rylo, I took your latest suggestion and it seemed to work great for the the first race. Then I tried to utilize the formulas for the other two races and I am coming up with discrepancies in 3rd-6th for the second race and 6th place for the 3rd race. The discrepancies are in red. And pertaining to Fotis' comment

Even that don't agree with all your manually putted results, take a look to this, pls..

from post#6, you were correct, I had a few issues with the manually putted results which should be corrected in this newly attached workbook.

In the actual workbook I utilize some named ranges so I recreated a mock up of the ones pertaining to these formulas and made the adjustments to utilize the names. I also added additional helper columns in I2:I21 and J2:J21 to match what was in H2:H21.

Thanks

Here is a "no helper column solution" (I think)
I put my results in J10:Q15 for comparison. They match on all races except "Sun Valley vs Octorara". However in that race, I see you put the time of 000200116 in second place and that time comes from Heat 2 so wouldn't it be ineligible?
Anyway, for 1st and second (example from J10 (SV vs Ken)
The arrayed formula of;

=INDEX($F$2:$F$21,MATCH(SMALL(IF($E$2:$E$21=1, IF($B$2:$B$21="Kennett",$F$2:$F$21,IF($B$2:$B$21="Sun Valley",$F$2:$F$21, 10000)),10000),ROW(A1)),$F$2:$F$21,0))

For 3rd onward (J12) the arrayed formula of

=INDEX($F$2:$F$21,MATCH(SMALL(IF($B$2:$B$21="Kennett",IF($F$2:$F$21=$J$10, 10000,IF($F$2:$F$21=$J$11,10000,$F$2:$F$21)),IF($B$2:$B$21="Sun Valley", IF($F$2:$F$21=$J$10, 10000,IF($F$2:$F$21=$J$11,10000,$F$2:$F$21)),10000)),ROW(A1)),$F$2:$F$21,0))

A simple INDEX/MATCH is used to pull over the team names (K10)
=INDEX($B$2:$B$21, MATCH(J10,$F$2:$F$21,0))
See attachment

Originally Posted by ChemistB

Here is a "no helper column solution" (I think)
I put my results in J10:Q15 for comparison. They match on all races except "Sun Valley vs Octorara". However in that race, I see you put the time of 000200116 in second place and that time comes from Heat 2 so wouldn't it be ineligible?

ChemistB - hey I see your here in NJ as well. Anywhere near the Paulsboro area? Anyway thanks for the comeback and you are correct with your question, which is one of the errors I corrected in my last post. I think we are on the same page with all the results except for place #6 in SV vs K where your result is coming back as .000200347 where I think it should possibly be .000200116 for SV?

ChemistB's suggestion ?

Hmmm, on my sheet, it is pulling back 0.000200116 for 6th between SV and K. Try copying J14 down into J15 again or double check the values in Converted Time. Fotis or Rylo? What are you seeing in my sheet in that spot?

I am near the Princeton area, a bit further north.

ChemistB - All is well. I had copied your sheet into the workbook with Fotis' and Rylo's suggestions and altered the formulas to include my named cells. It was an error on my end. I found it, made the correction an everything looks perfect.

Thanks very much everyone and have a great weekend.

That was a tough one. Glad we got it sorted out.

You guys always amaze me with what you can do with those array formulas! I need to find the time to sit down and learn more about them....