TEXTJOIN in a Spill Array Formula

Hi all,

I am trying to use TEXTJOIN() in a spill array formula but I kind of notice that it doesn't work very well with spill array, then I came with the formula below:

Formula:

=CHOOSE(ROW(P3#)-2,TEXTJOIN(" / ",,INDEX(E2#,,1)),TEXTJOIN(" / ",,INDEX(E2#,,2)),TEXTJOIN(" / ",,INDEX(E2#,,3)),TEXTJOIN(" / ",,INDEX(E2#,,4)))

The purpose of this formula is to spill an array for unique values in column A of the worksheet that match values in column B in a single cell. The range L2:M5 has an exemple of what I was looking for and range P2:Q6 is where I applied the formula. My problem is that my solution does not go to more then 4 lines without making it bigger and bigger.

Range E2:H19 and L14:l17 are my testing ground.

I am using Excel 365 in a Win7 then I can't join Office Insider for the new functions like LET().

Hi,

One way

In D2
=UNIQUE(Tabela1[ID])

In E2 copied down
=IFERROR(TEXTJOIN(" / ",TRUE,FILTER($B$2:$B$19,$A$2:$A$19=D2),""),"")

Please try

=LET(z,Tabela1,y,SORT(z),id,INDEX(y,,1),TRIM(MID(SUBSTITUTE(FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&id,MATCH(id,id,)=SEQUENCE(ROWS(z)))&" / "&INDEX(y,,2)),"/m","x",1)&"</m></x>","//m"),"/",REPT(" ",9),1),{1,9},{9,99})))

Or without Let

=TRIM(MID(SUBSTITUTE(FILTERXML(SUBSTITUTE(CONCAT(REPT("</m><m>"&SORT(Tabela1[ID]),MATCH(SORT(Tabela1[ID]),SORT(Tabela1[ID]),)=SEQUENCE(ROWS(Tabela1)))&" / "&INDEX(SORT(Tabela1),,2)),"/m","x",1)&"</m></x>","//m"),"/",REPT(" ",9),1),{1,9},{9,99}))

Not sure I completely understand, but try this in M3 and fill down.

Formula:

=TEXTJOIN(" / ",TRUE,INDEX(Tabela1[ESTADO],MATCH($L3,Tabela1[ID],0)):INDEX(Tabela1[ESTADO],MATCH(2,1/($L3=Tabela1[ID]))))