Concatenate multiple values into one cell, based on a lookup ID

Question

I'm trying to concatenate multiples values in one cell based on a lookup search (ID), the thing is this ID sometimes might be alone but other time might be between multiple IDs in the same cell (at the beginning, middle or end separated by commas). I've been using the below formula but only returns the first value when the ID is alone.

Current formula --> =TEXTJOIN(",",TRUE,IFERROR(XLOOKUP(A2,D:D,E:E),"ID not found"))

Hope you can help. Thanks.

Rows highlighted in blue, yellow and green are the expected results (I did them manually). Row 7 is the actual result (wrong/incomplete) for the current formula.

Answer 1

You can try below approach to get results:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(A2,D:D,1)),E:E,""))

One suggestion would be to limit the entire column usage to improve formula speed.

Answer 2

This can also be done via power query. I used different ID values from your table on the right, but it should still work correctly.

1. Create a table that has ID's in Col1 and the Value to be Returned in Col2
2. Open the table in Power Query (Data tab > Get & Transform Data via From Table/Range. If you don't have power query, reference this Complete Guide to Installing Power Query.

Power Query Steps

3. Change type of Col: Value to be Returned to text

4. Split Col: ID by Delimeter = ','

5. You know should have multiple ID columns (ID.X). Select all ID columns > Right click on header > select Unpivot Other Columns

6. Remove any unecessary columns

7. Select the column with all of your ID's > right click and select Group By

8. Under new column name, enter a new column header. Change the Operation to Sum. For Column, select your column that contains your Values to be Returned

9. Reference this guide for the next step. You need to manually configure the M code in the formula bar & change the formula from List.Sum([COL]).. to Text.Combine([COL], ",")..

10. The last step is to make sure that your new column is a text column, not a number.

I've attached a copy of my workbook, which should hopefully help. If not, I've pasted my code from the Advanced Editor below in case that is helpful. Be sure to update Table & Column names accordingly based on your workbook.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Value to be returned", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID.1", type text}, {"ID.2", type text}, {"ID.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Value to be returned"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Value", "Value to be returned"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Value"}, {{"Value.1", each Text.Combine([Value to be returned], ", "), type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Grouped Rows",{{"Value.1", type text}})
IN
#"Changed Type 2"

Let me know if this works or if you have any follow up questions.