Match value in column C to data in column A and return all data of column B

Question

I have an Excel file with (for example) 4 columns and I want to search column A for a value mentioned in column C and show in column D all the results that were found in column B separated by a ; and a space.

I got this partly working with the following formula:

=IF(C3<>"";TEXTJOIN("; ";TRUE;IF(($A$2:$A$6=$C2);$B$2:$B$6;""));"")

The thing is, this formula doesn't show any results when the search criteria is not 100% matching.

The following doesn't work:

=IF(C2<>"";TEXTJOIN("; ";TRUE;IF(($A$2:$A$6=("*"&$C2));$B$2:$B$6;""));"")

Answer 1

Perhaps:

=IF(C2<>"";TEXTJOIN("; ";TRUE;IF(ISNUMBER(SEARCH($C2;$A$2:$A$6));$B$2:$B$6;""));"")