index match to concatenate using UDF code

These formulae will EACH return ONE result. Indeed they will each return the same result. I think you need to explain what you are trying to do and (preferably) also attach a sample Excel sheet.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

That UDF is really only needed when concatenating multiple results from one formula. In your case, you are wanting to concatenate single results from a range of formulae. There no need for any macro in your file. A much simpler INDEX-MATCH will do to look up the individual results. To get you exact result this sort of structure was used:

=Formula1a&" blah blah /Prize $"&formula2a&"blah blah and $"and formula3a&char(10)&Formula 1b" blah blah..........

Where 1 is the formula for the name, 2 is the formula for the prize and 3 is the formula for the career earnings;
Where a, b, & c are the 9th, 10th and 11th series
Where CHAR(10) introduces a line break.

Example formula 1

Formula:

Please Login or Register  to view this content.

Example formula 2

Formula:

Please Login or Register  to view this content.

Example formula 3

Formula:

Please Login or Register  to view this content.

Total formula as used in the sheet:

Formula:

Please Login or Register  to view this content.

You're very welcome & thanks for the Rep.