Formula to select first 3 runners and first in each category

[Barryb]

Hi
I'm doing some road race results and would like to set up a formula on another sheet looking up the first 3 finishes, and then the first runner from each category. So I want to be able to have on another sheet looking up the results:

So below is an example of the race results and I would like on another sheet listing the top 3 male runners, 3 female runners and then 1st M40, M45, M50 etc etc. But ( looking at the list under the results) if a F40 comes 3rd I need her name in the top 3 finishes for female and also the 1st F40. I've tried and tried but just can't get it right.
Can anyone help please

No	Forename	Surname	Cat	Club	Time	Pos
157	Marc	Hartley	M	Stoke AC	38.4	1
123	Jimmy	Craig	M	Barlick Fellrunners	39.09	2
52	Nick	Treitl	M	Barlick Fellrunners	40.3	3
118	Robert	James	M	U/A	41.02	4
105	Michael	Keedy	M40	Skipton AC	41.27	5
101	Andy	Buttery	M40	Blackburn AC	41.45	6
70	Stephen	Stockwell	M45	West Australia	41.56	7
143	Thomas	Corrigan	M	U/A	42.03	8
18	John	Lloyd	M	Salford Harriers	42.43	9
1	Thomas	Brewster	M	Clayton le Moors Harriers	43.31	10
146	Gary	Pearse	M45	Blackburn AC	43.56	11
131	Kevin	Davies	M50	Clayton Harriers	44.14	12
69	Nicholas	Cook	M	Trawden AC	44.16	13
102	Sean	Molloy	M45	Rossendale	44.21	14
36	Andy	Berry	M	Barlick Fellrunners	44.24	15
39	Chris	Quigley	M45	Trawden AC	44.27	16
156	Andrew	Driver	M	Barlick Fellrunners	44.38	17
111	Paul	Hesketh	M45	Clayton Harriers	44.43	18
119	Paul	Redman	M40	Trawden AC	44.45	19
83	Thornton	Talyor	M55	Rossendale	44.57	20

	No	Forename	Surname	Cat	Club	Time	Pos
1st Male	157	Marc	Hartley	M	Stoke AC	38.4	1
2nd Male	123	Jimmy	Craig	M	Barlick Fellrunners	39.09	2
3rd Male	52	Nick	Treitl	M	Barlick Fellrunners	40.3	3
1st M40
1st M45
1st M50
1st Female	66	Sarah	Tipler	F	Barlick Fellrunners	47.05	31
2nd Female	17	Autumn	Howard	F	Trawden AC	49.57	54
3rd Female	38	Lorraine	Slater	F40	Barlick Fellrunners	51.54	67
1st F40	38	Lorraine	Slater	F40	Barlick Fellrunners	51.54	67
1st F45
1st F50

 

[navic]

So below is an example....

You should set an example of your file, then it could be easier to help you.
You try my idea
I would have tried to solve the following way

On Sheet1 I have added four extra columns, these columns you can hide to be invisible

On Sheet2 I put the following formulas

The first category of runners
In cell B2 array CSE formula

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L2=Sheet1!$D$2:$D$21)*(ROW(Sheet1!A1)=Sheet1!$J$2:$J$21);0))

In cell C2, this formula you copy to the last column to the right and down for all categories

=VLOOKUP($B2;Sheet1!$A$2:$G$21;COLUMN(Sheet1!B$1);FALSE)

The second category of runners
In cell B6 array CSE formula

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L6=Sheet1!$D$2:$D$21)*(ROW(Sheet1!$A$1)=Sheet1!$K$2:$K$21);0))

The third category runners
In cell B11 array CSE formula

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L11=Sheet1!$I$2:$I$21)*(ROW(Sheet1!A2)=Sheet1!$J$2:$J$21);0))

The fourth category runners
In cell B15 array CSE formula

=INDEX(Sheet1!$A$2:$A$21;MATCH(1;(Sheet1!L15=Sheet1!$D$2:$D$21)*(ROW(Sheet1!$A$1)=Sheet1!$K$2:$K$21);0))

To resolve the errors you use IFERROR or IF/ISERROR
I hope I helped you, if this is not the solution I gave you an idea

Pay attention to the separator argument inside a formula. You may need to use a comma (,) instead of a semicolon (