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!

XOR LX · Accepted Answer · 2014-06-04 19:11:15Z

2

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

answered Jun 4, 2014 at 19:11

XOR LX

7,7821 gold badge19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3144644 Over a year ago

This is great thanks - any chance you know how to modify the second formula so that if the length of the substring is 1 or 2 - the formula looks for an exact match between the substring and the brand list (e.g., a returned brand would need to be exactly the same value), if the length of the substring is 3 or 4, then the left 3 or 4 characters in the brand code need to match exactly, and if the length is 5 or more, it returns what you have now. Thanks! I played around with this but so far no luck. (Lot of false positives I'm trying to fix)

XOR LX Over a year ago

Becoming complicated! =IFERROR(IF(LEN(D5)=2,INDEX($A$5:$A$7655,MATCH(D5,$A$5:$A$7655,0)),IF(OR(LEN(D5)={3,4}),INDEX($A$5:$A$7655,MATCH(D5,INDEX(LEFT($A$5:$A$7655,CHOOSE(1+(LEN(D5)=4),3,4)),,),0)),INDEX($A$5:$A$7655,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(D5,$A$5:$A$7655)),,),0)))),"No Match Found")

XOR LX Over a year ago

Can you tell what type of error, on which string you're getting the error, what you think the result should instead be, and why?

user3144644 Over a year ago

Sorry for the delay here - Excel can't even execute the function - says there's an error with the function. Seems to be an error in the middle clause with the 3 or 4 conditions. I tried to recreate myself but still seeing error.

user3144644 Over a year ago

Nevermind - seems like you added an extra ( - got it! Thanks!

|

Gary's Student · Accepted Answer · 2014-06-04 17:18:11Z

1

In E5 enter:

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

and copy down.

answered Jun 4, 2014 at 17:18

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Brilliand · Accepted Answer · 2014-06-04 16:59:27Z

0

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 "".)

answered Jun 4, 2014 at 16:59

Brilliand

13.8k6 gold badges49 silver badges58 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related