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!
Finding the last matching string is actually slightly more straightforward in terms of the formula:
=IFERROR(LOOKUP(2^15,SEARCH(D5,$A$5:$A$7655),$A$5:$A$7655),"No Match Found")
For finding the first matching string:
=IFERROR(INDEX($A$5:$A$7655,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(D5,$A$5:$A$7655)),,),0)),"No Match Found")
Regards
In E5 enter:
=IFERROR(OFFSET(A5,MATCH("*" & D5 & "*",A5:A7655,0)-1,0),"")
and copy down.
I would suggest doing this with a two-dimensional grid. Use the brand names as the left column (the row labels), and the substrings as the top row (the column headers). Then the formula in B2 would be:
=IF(ISERROR(FIND(B$1,$A2)),B$1,$A2)
Fill that formula across and down, and the row at the bottom will be filled with the last brand name found for each substring. If no brand name is found, it will simply return the substring. (You could make it return empty instead by changing the formula in the first row after the fill down is complete - replace B$1 with "".)
