Array formula returning #VALUE when using large text-based field

[joerames]

Working on a report for a customer.  Basic issue is data coming through on multiple rows and customer wants consolidated on one row.  My reporting tool doesn't allow me to consolidate to one row as their query requires.  Couple of approaches, one using pivot, one using arrays INDEX/MATCH/TEXTJOIN.  

So I have a set of data on one sheet in a table.  It's not huge (<1000 rows).  On the other, I've identified the unique reference numbers and placed in column A.  I then go across and do simpler INDEX/MATCHes to get the data in columns where it is the same on all rows.  For the columns where I need to consolidate the 1+ rows, I use the TEXTJOIN.  

{=TEXTJOIN("--",TRUE,IF([@[Assignment ID]]=AdhocTable[Assignment ID],IF(AdhocTable[Immigration Status Update.Comments]=0,"",AdhocTable[Immigration Status Update.Comments]),""))}

It's actually working great, except for a few rows.  The .Comments field is free form text and can be long.  The longest successfully working is 254.  Some go up to 1500 in LEN.

I can see the #VALUE in the array when I evaluate the formula for one with a long text field.  Am I running up against some limitation for arrays when using text fields?

Pivot works, okay.  But it's not quite as attractive.

[#2]

@joerames , great finding, I didn't know about that. Yes, it looks like conditional functions as IF() return max 255 characters length text as an array element.

I played with simple model

the workaround could be

=TEXTJOIN("--",TRUE,LEFT($C$3:$C$5,IF($D$3:$D$5="a",LEN($C$3:$C$5),0)))

(CSE formula).

TEXTJOIN itself returns #VALUE if the resulting string is more than 32K length, don't remember exactly.

[#3]

@Sergei Baklan Sorry for the slow reply.  I haven't quite figured out how to use your advice.  I've attached a sample file.  I messed with it quite a bit.  Not an expert here.

The file has 3 cases.  One is working.  One doesn't error, but returns 0's and I don't want that.  The final case shows the long field and it #ing out.

Would love for you to take a peak and help me along!

[#4 · Accepted solution]

@joerames 

The formula could be

=TEXTJOIN(CHAR(10),TRUE,LEFT(AdhocTable[Immigration Status Update.Comments],IF([@[Assignment ID]]=AdhocTable[Assignment ID],LEN(AdhocTable[Immigration Status Update.Comments]),0)))

entered as array one (Ctrl+Shift+Enter). I take CHAR(10) (new line) as separator, you may change on any other symbol or text.

Please check attached.

[#5]

@Sergei Baklan   Brilliant!