Extract names from string based on value list

  • Thread starter Thread starter Pluggie
  • Start date Start date
P

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?
 
One way
Assume productnames running in A2 down
Assume D2:D10 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
 
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.
 
Back
Top