Merging of selected strings – something like CONCATENATEIFS, if it existed

This article describes how to merge texts, that meets some criteria. It is similar to SUMIFS or COUNTIFS (but works for text merging instead of calculating) or CONCATENATEX from DAX. 

In this example we are going to merge all comments related to product written in A2:

How to do it? Lets write this matrix formula:

{=TEXTJOIN(“; “;1;IF(D:D=A2;E:E;””))}

Why?

• Curly brackets indicate a matrix formula. This is supposed to be confirmed by Ctrl Shift Enter instead of Enter (this confirmation adds curly brackets).
• TEXTJOIN is a function, that merges texts. It can contain a merging text string – which separates the merged strings in result. This function is not available in all Excel versions.
• IF is a function, saying whether some text is or is not included in final merged text.

Note

• Similarly to TEXTJOIN you can use CONCAT (which is not the same as CONCATENATE…)