How can I get Concat to spill

[Ngamia]

Hi all,
I am trying to concatenate some text with some values using the formula below in column "M" and have the result as a spill array. Using this formula, all I get is a #REF! error.

=CONCAT("H35/",(TEXT(K2#,"0000000000000")),"-",L2#,"/00")


Columns "K" and "L" both contain spill arrays with the formulas as shown below
Column K: =UNIQUE(J:J,,TRUE)
Column L: =XLOOKUP(K1#,ZeeReport[Name],ZeeReport[Suffix],"No Suffix",0)


Columns "K" changes in length based on what is in column "J" so I would like column "M" to also do the same.

Any help will be greatly appreciated.
N

 

[JvdV]

Don't use CONCAT(), just the ampersand will do:

="H35/"&TEXT(K2#,"0000000000000")&"-"&L2#&"/00"

 

[Ngamia]

Don't use CONCAT(), just the ampersand will do:

="H35/"&TEXT(K2#,"0000000000000")&"-"&L2#&"/00"

Thank you so much.
I have never used & in my formulas as concatenate and (now) concat always worked well.
However, with the introduction of array functions I am having to think quite differently about formulas.