If Match then return TEXTJOIN

You'll need VBA for that. Paste this code into a module and save as Macro enabled.

Please Login or Register  to view this content.

The formula needed is then:

=concatall(IF($C$2:$C$19=C2,$A$2:$A$19,"")," ")

where the last " " defines the spacer between each answer: in your case, a single space. This is an array formula.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work... Remember to enable macros when you open the attached file.