Formula needed

[76chickens]

Please can anyone help me to make a formula for this sheet? Thanks in advance!

 

[DanteAmor]

You need several formulas and order the data as follows:

Excel Workbook
	A	B	C	D	E	F	G	H
1					People who scored
2		Name	Score		3	4	5	6
3		Joanna	4		Emma	Joanna	step	Vane
4		Emma	3		Ben	Carol	Simon
5		step	5			Louise
6		Vane	6
7		Carol	4
8		Ben	3
9		Simon	5
10		Louise	4
Hoja7

[TABLE="class: grid, width: 500"]
[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]=IF(D2="",MIN($C$3:$C$10),IF(D2<MAX($C$3:$C$10),D2+1,""))[/TD]
[/TR]
[/TABLE]


Copy to the right until the first blank cell appears

[TABLE="class: grid, width: 700"]
[TR]
[TD]Cell[/TD]
[TD]Array Formula[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]{=IFERROR(INDEX($B$1:$B$10, SMALL(SI($C$3:$C$10=E$2, ROW()), ROW()-2)),"")}[/TD]
[/TR]
[/TABLE]

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself



Copy the formula down to row 10, edit the formula and press Shift + Control + Enter. Then copy the formulas from E3:E11 to column H

 

[DanteAmor]

Sorry, didn't paste the E2 formula, take the next one:

Cell E2

=IF(D2="",MIN($C$3:$C$10),IF(D2<max($c$3:$c$10),d2+1,""))

<max($c$3:$c$10),d2+1,""))
< MAX($C$3:$C$10),D2+1,""))</max($c$3:$c$10),d2+1,""))
</max($c$3:$c$10),d2+1,""))

 

[76chickens]

Thank you DanteAmor, will give it a try!

 

[76chickens]

Can't get it to work but it's harder than stuff I usually do. If I want to watch a video about these type of formulas what should I search? If error function?

 

[DanteAmor]

Review my file

https://www.dropbox.com/s/vtt9fvf52rn4wnc/score.xlsx?dl=0

 

[76chickens]

Amazing Dante! What is the secret with the ctrl+shift+enter thing? Is that where I was going wrong adding the formula?

 

[76chickens]

I've tried to adapt it to the actual document but can't get it to work. I've had to delete loads of information as I'm a teacher but hope I've left enough for it to make sense? I'm trying to feed a summary sheet from many other sheets in the document. Totally understand if you don't have time to look as you have been more than generous with your time already! https://drive.google.com/open?id=1ECB6qtxD0tlOdFjwUWO6SNvw_VeqHAbg

 

[aqeelnokia99]

=IFERROR(INDEX($C$3:$C$10,AGGREGATE(15,6,ROW($D$3:$D$10)-ROW($D$3)+1/($D$3:$D$10=F$2),ROWS($F$2:F2))),"")