Formula Query: IF, CONCATENATE or INDEX MATCH?

[olimajor123]

Hi,

I'm trying to calculate a formula to help me with tracking some football scores.

Please see the attached sample.

I have a sheet that tracks the team members and every gameweek across the season, in particular the goalscorers.
For example if you look in Column E, three goals were scored by two different members of the team (one scoring one and one scoring two).

I want to create a formula where the highlighted cell in yellow will auto-fill the with goalscorers. For example for this fixture in gameweek three it should fill "Andrew, Dean (2)".

This means that on the master spreadsheet I have with the seasons fixtures I can just enter the scorer names on the table and they will auto-fill into the results on a separate tab.

Help with the formula is appreciated! I think it will need an IFERROR prefix so that it appears blank if none of the cells contain any text,

Attachment link is below (don't know how to add on here!)

https://www.dropbox.com/s/y90l9lwt711hi1e/Sample.xlsx?dl=0

Thanks

 

[Special-K99]

Concatenating cells with a comma separator is best done with VBA, though I dont have time to spend on that.