Extract names from string based on value list

[Pluggie]

I have a list of productnames and a list of brandnames.

I want to supply the productnames with an attribute "Brand".

I am currently manually filtering the list of productnames based on
"Contains... Brand" and adding the Brand to the product using copy paste,
but this takes me too long and I can only match records one brand at a time.

Can I make Excell search through the list of productnames and whenever it
comes across a brandname within the string of a productname it will add it to
the column next to it?

 

[Max]

One way
Assume productnames running in A2 down
Assume D210 contains all the brands text

In B2, normal ENTER:
=INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0))
Copy down to return required results

If you need an error trap to return neat looking blanks for unmatched cases
(if any), use this in B2, normal ENTER, copied down:
=IF(ISNA(MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)),"",INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0)))

Modify to suit. Voila? hit the YES below

 

[T. Valko]

For the error trap, try this:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX($D$2:$D$10,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($D$2:$D$10,A2)),),0))))

Only works with text.