excel - Array formula result concatenated into single cell

Question

Is it possible to take the return values from an array formula and concatenate them into a single cell? For example, I have a simple spreadsheet with rows for project tasks which looks like this:

Task #    Description    Blocked on    Blocking
-----------------------------------------------
1         Task 1         2             
2         Task 2                       $formula
3         Task 3         2             

I would like the formula in cell D3 to return "1, 3" (it would also be great to put multiple values in the Blocked On cell). I've currently got it returning "1" with the following formula:

=(INDEX($A2:$A999,MATCH(A3,$C2:$C999,0)))

Answer 1

the formula you are looking for is (in d3): =IF($C$2:$C$999=$A4;$A$2:$A$999;"") +ctrl+shift+enter

make sure you enter it as an array

(NOTE: you may also need to change ; to , depending on your regional settings)

This will return content of A if data in C matched current Ai. You can see only one result now, since the result is array, but if you use Transpose() and select multiple cells in same row, then with F2 then ctrl+shift+enter you will see all results in a row! Ex: select D3:K3, press F2, write formula: =Transpose(IF($C$2:$C$999=$A4;$A$2:$A$999;"")), press ctrl+shift+enter... (again you may need to write: =Transpose(IF($C$2:$C$999=$A4,$A$2:$A$999,""))...)

but you must have many columns to the right since empty results still occupy columns! Even if empty strings are handled to occupy no columns, there still remains the matter of unknown results-columns. So I propose usage of a VBA function (one I wrote quite a while ago) to Concatenate valid results in one cell (with any delimiter you like).

Final proposed formula in D3: =MyConCat("-";IF($C$2:$C$999=$A2;$A$2:$A$999;"")) +ctrl+shift+enter

and code for MyConCat (to place in a module in VBA code area):

' MyConCat
'
' Very simple By Apostolos Goulandris
Function MyConCat(myDelimiter As String, Avar) As String
    Dim b As Variant, Dum As String
    If IsMissing(myDelimiter) Then myDelimiter = ""
    For Each b In Avar
        Dum = IIf(Len(b) > 0, Dum & myDelimiter & b, Dum)
    Next
    MyConCat = IIf(Len(myDelimiter) > 0, Mid(Dum, Len(myDelimiter) + 1, Len(Dum)), Dum)
End Function