Receiving duplicates with my formula

[sugarwhitesand]

I am receiving duplicates and I cannot figure out why. I would also like the #N/A values to be blank, since I do need the formula down all of column C. You know, I might even be using the wrong formula!

What is my goal?

I have over 1,000 competitor names and each competitor will be placed into an 'Event' (picture 1). These 'Event' names are placed in cell B5 of each worksheet (shown in pictures 2 and 3), up to 40 total events (worksheets).

So, in the first picture, Todd (D14) is in Event A (E14). I need Todd's name to show up in C9 of the 2nd picture, which with my formula it has; but, if you look down the following cells of the 2nd picture and into the 3rd picture, the names start duplicating. Then, when there are no further competitor names for events A and B, I receive #N/A values; which I would like to remain blank.​

Below is the code for C9 (2nd picture)

=INDEX('COMPETITOR NAMES ~ DIVISIONS '!D14:E$1201,MATCH('(First Event) ~ MAIN WKSHT'!$B$5,'COMPETITOR NAMES ~ DIVISIONS '!E14:E$1201,0),1)

Below is the code for C10 (2nd picture)

=INDEX('COMPETITOR NAMES ~ DIVISIONS '!D15:E$1201,MATCH('(First Event) ~ MAIN WKSHT'!$B$5,'COMPETITOR NAMES ~ DIVISIONS '!E15:E$1201,0),1)

Thank you for your time and help! I greatly appreciate it!

 

[NARAYANK991]

Hi ,

First , instead of spending so much of time and effort , it would help matters if you could upload a file ; much faster , and much more beneficial to you and to other members who would like to help.

Second , most probably you are using the wrong formula ; using MATCH will always return only one result which will be the first match ; copying a formula down will not change anything , since the MATCH function will return the same result where ever it is used.

If you upload your workbook , you can get your answer fast.

Narayan