Search code examples
excelexcel-formuladynamic-arrays

Excel - Concat values from multiple dynamic arrays into single cell

I have 3 dynamic array formulas.

In the below example, they would be the columns "X", "Y", and "Type".

For the purposes of example answers, let's use X#, Y#, and Type# as the cell references.

I want a 4th dynamic array formula that concatenates the 3 results, with delimiters, into a single 4th column.

X Y Type Desired Output
0 3 abcd 0/3_abcd
1 4 efgh 1/4_efgh
2 5 ijk1 2/5_ijkl

I've tried to HSTACK() the 3 columns then use BYROW() on the result but couldn't figure out the LAMBDA part of it. Or even if it can be used like that. This feels like something that should be blindingly easy, but I just can't figure out the syntax to describe to Excel what I want it to do.

Solution

  • Try using the following formula:

    enter image description here

    Formula used in cell D2

    =A2#&"/"&B2#&"_"&C2#

    LAMBDA() iteration is not needed as far I see however if one insist then can use MAP()

    =MAP(A2#,B2#,C2#,LAMBDA(x,y,z,x&"/"&y&"_"&z))