Display all matches if cell contains text from list

I recorded a macro, which returns the matching value of column B for column A. The column A is a dynamic list, which i update everyday and column B is static (will always remain the same).

For eg: If in column A, i have ABC, DEF, GHI and in column B I have ABC and DEF. It returns the first value listed in column B, which is "ABC". However, I want the formula to return all matching values for column A, which are ABC and DEF in this case.

The formula I am using is

=IFERROR(INDEX(abc_l, SMALL(IF(COUNTIF($E153, "*"&abc_l&"*"), MATCH(ROW(abc_l), ROW(abc_l)), ""), COLUMNS($A$1:A152))), "")

where abc_l is a defined list.

PS: I am an excel noob with little knowledge of VBA

Thank you !

UDF

Use in cell like
=MatchMulti(A2:A6,B2:B5,",")

To a stabdard code module

Please Login or Register  to view this content.

You need to upload a workbook with before/after clearly showing what you are trying to do.

Thanks Jindon. I think i figured it out now. I am able to return all matching values. I used this formula

=TEXTJOIN(", ", TRUE, IF(COUNTIF(E2, "*"&abc_l&"*"), abc_l, ""))