Resolving Duplicate name is spreadsheet that has index and if statements

[FVZBW]

Hi All,

can anyone assist, I have been helping out our local archery club with score keeping and have crudely made a simple system, however if there are two people with the same score the system puts the first name twice.

any assitance will be greatly appreciated.

Test.xlsm
	EG	EH	EI	EJ	EK	EL	EM	EN	EO
20	Combined Team Prize Club -		Active Generation			Combined Team Prize Club -		Valley Academy
21	Position	Score	Name	Group		Position	Score	Name	Group
22	1	172.00	Lena Famien	Cub/Beginner		1	137.00	Lefika Ikaapoleng	Cub/Beginner
23	2	167.00	Annika Potgieter	Cub/Beginner		2	112.00	Zulandri Du Plessis	Cub/Beginner
24	3	163.00	Rameen Suleman	Cub/Beginner		3	97.00	Christine Kalafatis	Cub/Beginner
25
26
27
28	1	401.00	Aiden van Zyl	JNR		1	543.00	Tlotso Seipone	JNR
29	2	401.00	Aiden van Zyl	JNR		2	532.00	Willie Herbst	JNR
30	3	279.00	Jamie van Zyl	JNR		3	531.00	Zarita Du Plessis	JNR
31	1	544.00	Rourke Basson	SNR		1	568.00	Caden De John	SNR
32	2	537.00	Benjamin van Wyk	SNR		2	554.00	Reggy ter Hofte	SNR
33	3	519.00	Chanriette van Nieuwenhuize	SNR		3	554.00	Reggy ter Hofte	SNR
34	1	553.00	Nabeel Pahmy	AAG		1	571.00	Kathryn Bezuidenhout	AAG
35	2	538.00	Vaughan Strugnell	AAG		2	570.00	Nicoline Goosen	AAG
36	3	528.00	Rapelang Menong	AAG		3	564.00	Leslie Bezuidenhout	AAG
Score

Cell Formulas
Range	Formula
EI20,EN20	EI20	=EI3
EH22:EH24	EH22	=LARGE(IF($N$5:$N$120=$EI$3,$DX$5:$DX$120),EG22)
EI22:EI24	EI22	=INDEX($K$5:$K$120,MATCH(LARGE(IF($N$5:$N$120=$EI$3,$DX$5:$DX$120),$EB22), IF($N$5:$N$120=$EI$3,$DX$5:$DX$120),0))
EM22:EM24	EM22	=LARGE(IF($N$5:$N$120=$EN$3,$DX$5:$DX$120),EL22)
EN22:EN24	EN22	=INDEX($K$5:$K$120,MATCH(LARGE(IF($N$5:$N$120=$EN$3,$DX$5:$DX$120),$EB22), IF($N$5:$N$120=$EN$3,$DX$5:$DX$120),0))
EH28:EH30	EH28	=LARGE(IF($N$122:$N$162=$EI$3,$DX$122:$DX$162),EG28)
EI28:EI30	EI28	=INDEX($K$122:$K$162,MATCH(LARGE(IF($N$122:$N$162=$EI$3,$DX$122:$DX$162),$EB28), IF($N$122:$N$162=$EI$3,$DX$122:$DX$162),0))
EH31:EH33	EH31	=LARGE(IF($N$164:$N$214=$EI$3,$DX$164:$DX$214),EG31)
EI31:EI33	EI31	=INDEX($K$164:$K$214,MATCH(LARGE(IF($N$164:$N$214=$EI$3,$DX$164:$DX$214),$EB31), IF($N$164:$N$214=$EI$3,$DX$164:$DX$214),0))
EH34:EH36	EH34	=LARGE(IF($N$216:$N$271=$EI$3,$DX$216:$DX$271),EG34)
EI34:EI36	EI34	=INDEX($K$216:$K$271,MATCH(LARGE(IF($N$216:$N$271=$EI$3,$DX$216:$DX$271),$EB34), IF($N$216:$N$271=$EI$3,$DX$216:$DX$271),0))
EM28:EM30	EM28	=LARGE(IF($N$122:$N$162=$EN$3,$DX$122:$DX$162),EL28)
EN28:EN30	EN28	=INDEX($K$122:$K$162,MATCH(LARGE(IF($N$122:$N$162=$EN$3,$DX$122:$DX$162),$EB28), IF($N$122:$N$162=$EN$3,$DX$122:$DX$162),0))
EM31:EM33	EM31	=LARGE(IF($N$164:$N$214=$EN$3,$DX$164:$DX$214),EL31)
EN31:EN33	EN31	=INDEX($K$164:$K$214,MATCH(LARGE(IF($N$164:$N$214=$EN$3,$DX$164:$DX$214),$EB31), IF($N$164:$N$214=$EN$3,$DX$164:$DX$214),0))
EM34:EM36	EM34	=LARGE(IF($N$216:$N$271=$EN$3,$DX$216:$DX$271),EL34)
EN34:EN36	EN34	=INDEX($K$216:$K$271,MATCH(LARGE(IF($N$216:$N$271=$EN$3,$DX$216:$DX$271),$EB34), IF($N$216:$N$271=$EN$3,$DX$216:$DX$271),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RobP]

The reason for lack of response is likely as you have not shared the full sheet (meaning formulas you use are referring to areas on your worksheet that people on this forum cannot see and therefore understand).

=INDEX($K$122:$K$162,MATCH(LARGE(IF($N$122:$N$162=$EI$3,$DX$122:$DX$162),$EB28), IF($N$122:$N$162=$EI$3,$DX$122:$DX$162),0))

When you use Index / Match statements, it always uses the first match that is comes across in a list, so if there are 3 or 4, it uses the first one each time, as you can see. So its doing what it thinks it should.

With more sheet info as to what you are doing, maybe people could propose an alternative solution for you ..

cheers
Rob