excel - How to find if substring exists in a list of strings (and return full value in list if so)

Question

I have a list of brand names in A5:A7655 I have a list of potential substrings in D5:D1400

I need to find a corresponding brand name for each substring where possible. Most of these substrings are contained in one of the cells in the brand name list. Returning the first brand name on the list that constains the substring is fine.

For example: My substring in D5 is "ABC Studios" - if i search my whole list I see that there is a brand name "ABC Studios, LLC". In E5 I want to return ABC Studios, LLC. If there is no match in the list returning 0 or something is fine.

I played around with =ISNUMBER(SEARCH(A5,$D$5:$D$1400)) with no success. Thanks!

Answer 1

In E5 enter:

=IFERROR(OFFSET(A5,MATCH("*" & D5 & "*",A5:A7655,0)-1,0),"")

and copy down.