Help Finding One of a group of words in a given cell

[chrs0049]

I currently have a list of list of product names in column A:

i.e.
Airborne
Blackberry
Care One
Esskay
Swing Line
Verizon 3G

And a list of "buzz words" in column B:

i.e.
Air
Line
berry

(Both lists are much longer, but just giving you a sample). What I need to do is search each cell in column A to see if it contains ANY of the words in column B. So in this case Airborne, Blackberry, and Swingline would all get 'flagged'.

I found this formula in an old thread:
=OR(ISNUMBER(LOOKUP(2^50,SEARCH("buzz words","A1"))))

But all that does is return a "TRUE" or "FALSE". What I need is for it to return the value which it finds in the A cell. For example, when searching "Blackberry" I need the formula to return "berry" instead of "True"

Does that make sense? The buzz word list is much longer (50-100) words, so I have to rule out using a series of IF statements for each word.

 

[Jonmo1]

Welcome to the board...

See if this thread helps.

http://www.mrexcel.com/forum/showthread.php?t=547630

 

[jim may]

Excel Workbook
	A	B	C
1	Airborne	Air	Air
2	Blackberry	Line	Line
3	Care One	berry	berry
4	Esskay
5	Swing Line
6	Verizon 3G
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(FIND(B1,A$1:A$6))>0,B1,"")
C2	=IF(ISNUMBER(FIND(B2,A$1:A$6))>0,B2,"")
C3	=IF(ISNUMBER(FIND(B3,A$1:A$6))>0,B3,"")

 

[Andrew Poulsom]

Jim, you rearch is the worng way round? The OP wants to find A1 in column B and return what's in column B.

 

[chrs0049]

Welcome to the board...

See if this thread helps.

http://www.mrexcel.com/forum/showthread.php?t=547630

Hey guys, I think the answers in this thread are going to do the trick. Sorry for the double post, and appreciate the help!