Concatenating multiple cells in one column based on condition on list of strings in another

Given a spreadsheet where we have columns as follows:

A,     ..., P,     ..., S,                   ...
Test1, ..., Name1, ..., Test1, Test2, Test3, ...
Test2, ..., Name2, ..., Test3,               ...
Test3, ..., Name3, ..., Test1, Test3,        ...

I would like to create a list of values from Column P where A is in the list S1. For example, for the column A value Test1, a new column T would contain the following: Name1, Name3 because Test appears in both rows in S.

>Solution :

Excel MS365 formula:

I feel like the following could point you in the right direction:

Formula in D1:

=MAP(A1:A3,LAMBDA(a,TEXTJOIN(", ",,REPT(B1:B3,ISNUMBER(FIND(", "&a&",",", "&C1:C3&","))))))

PowerQuery:

If you don’t have access to above mentioned functions, consider to work your way around this using PowerQuery, a powerfull tool to do this quite elegantly too:

Here the column ‘custom’ is created through:

= let s=[Column1], c2=Table.Column(Source, "Column2"), c3=Table.Column(Source, "Column3") in Text.Combine(List.Transform(List.Numbers(0,List.Count(c2)), each if List.Contains(Text.Split(c3{_},", "),s) then c2{_} else null),", ")

The completed M-code could look like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Ans = Table.AddColumn(Source, "Custom", each let s=[Column1], c2=Table.Column(Source, "Column2"), c3=Table.Column(Source, "Column3") in Text.Combine(List.Transform(List.Numbers(0,List.Count(c2)), each if List.Contains(Text.Split(c3{_},", "),s) then c2{_} else null),", "))
in
    Ans

Obviously I’ve used fictional column names, just make sure to edit the above to suitable named table and columns.