Seaching a list of strings againist a list of words and returning a value (Excel)

Question

In excel I have two sheets. In Sheet1 Column A, I have strings. In Sheet2 I have two columns, Column A contains one word that should potentially be somewhere in a string from Sheet1, and Column B has a return value that I would want if Sheet2!A:A was found anywhere in the strings in Sheet1!A:A. For Example:

Sheet 1:

Col. A                             **Col. B (These are the results I WANT from Sheet2)**
stackoverflow excel blue pony        Group2
grapes monkey help me                Group1
random words not very creative       Group3

And in Sheet 2, you have

 Col. A     Col. B
 monkey     Group1
 excel      Group2
 creative   Group3

I have been messing around with what basically comes down to a bunch of different iterations of a vlookup for Sheet1!B:B, but I can never get it to work right. The reason I have been focusing on vlookups is because of the need to return the value in Sheet2!B:B if Sheet2!A:A if found anywhere in Sheet1!A:A. I'm sure there is probably something much better out there for this solution than that, I am just stuck on this. Any help would be greatly appreciated.

Answer 1

Revised formula due to revised question

Try this formula in Sheet1 B2 copied down

=LOOKUP(2^15,SEARCH(Sheet2!A$1:A$4,A2),Sheet2!B$1:B$4)

That should work for your example but in a real situation you might get false matches because a search value of "key", for example, will match with "monkey". To prevent that you can use this version which only matches with whole words:

=LOOKUP(2^15,SEARCH(" "&Sheet2!A$1:A$4&" "," "&A2&" "),Sheet2!B$1:B$4)

Answer 2

A little VBA might be your best bet here...

Sub finder()

Dim ws1, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

Dim i As Integer

i = 1

Do Until ws2.Cells(i, 1).Value = ""
'The next line takes the value from sheet2, looks for it in sheet1 then puts 
'the value of the cell directly adjacent back into sheet2. This may fail if a 
'value is not found.

ws2.Cells(i, 2).Value = ws1.Range("A:A").Find(ws2.Cells(i, 1).Value).Offset(0, 1).Value
    i = i + 1
Loop

End Sub