How can I remove all the duplicates out of the results of the following formula:
=IF(C2<>"";TEXTJOIN("; ";TRUE;IF(ISNUMBER(SEARCH($C2;$A$2:$A$6));$B$2:$B$6;""));"")
See Cell D2 in the picture below for an example
My version of Excel doesn't have the Unique function.
Well, you could use FILTERXML and use an XPATH expression to return unique nodes:
=IFERROR(TEXTJOIN(", ",,FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,IF(ISNUMBER(SEARCH($C2,$A$2:$A$6)),$B$2:$B$6,""))&"</s></t>","//s[not(preceding::*=.)]")),"")
Here we check for all <s> child-nodes and exclude them if any preceding sibling is the same.
Another option would be to avoid the nested IF and let XPATH do all the work. Might be an option if you'd have a reasonable list (not massive):
=IFERROR(TEXTJOIN(", ",,FILTERXML("<x><t>"&TEXTJOIN("</s></t><t>",,B$2:B$6&"<s>"&A$2:A$6)&"</s></t></x>","//t[.//*[contains(.,'"&C2&"')]][not(preceding::*=.)]")),"")
Here we check for all <t> nodes and check if their child node contains our search value. Furthermore it does the same check on duplicates as before.