looking for keywords on two spreadsheets

[thebeans]

If a keyword on Sheet 1 C6 is contained within a cell in a column on Sheet 2 B:B, I want to copy the value in another column (G) in Sheet 2 to sheet 1. How can I do this? I have tried VLookUp and others but without success as they only work when the keyword exactly matches the target cell, which it doesn't in sheet 2
thanks thebeans

 

[L. Howard]

If a keyword on Sheet 1 C6 is contained within a cell in a column on Sheet 2 B:B, I want to copy the value in another column (G) in Sheet 2 to sheet 1. How can I do this? I have tried VLookUp and others but without success as they only work when the keyword exactly matches the target cell, which it doesn't in sheet 2
thanks thebeans

Post a few examples of the keyword you would expect to show up in sheet1-C6, and a few examples of the strings in the target cells on sheet2 B:B.

Maybe using Instr in a VBA solution or a formula something like this:

=COUNT(FIND("Kentucky",Sheet2!B8))>0

Where sheet2 - B8 has the string Kentucky Blue Grass.

Howard

 

[thebeans]

thanks for the response L. Howard
soo in sheet1 C6 I have a horses name i.e 6. Cha Siu Bao
then in sheet2 B2 I have 12:43 Ball (AUS) 31st Oct\R5 450m Mdn\1. Winlock Stanley . I wont to match the names only then if a match copy value of G2 to sheet1 K6

<colgroup><col></colgroup><tbody>
</tbody>

thanks the beans

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[L. Howard]

If I understand correctly, try this in K6 of sheet1.

=IF(COUNT(FIND(C6,Sheet2!B2))>0,Sheet2!G2,"")

If no match, then it returns nothing.

Where I am assuming that the horse name in your example and the sheet2 B2 example would NOT be a match and therefore return nothing.

6. Cha Siu Bao does not equal 1. Winlock Stanley

Howard

 

[thebeans]

thanks for the reply again L.Howard . I have tried the above but when I copy down I don't get any value backon all 2200 rows. I did try this =IF(COUNT(FIND(C6,SHEET2!C:C))>0,SHEET2!G,"") sry I had the B2 wrong is C but still no luck

thanks thebeans

 

[Aladin Akyurek]

If a keyword on Sheet 1 C6 is contained within a cell in a column on Sheet 2 B:B, I want to copy the value in another column (G) in Sheet 2 to sheet 1. How can I do this? I have tried VLookUp and others but without success as they only work when the keyword exactly matches the target cell, which it doesn't in sheet 2
thanks thebeans

Try in D6 of Sheet1...

=LOOKUP(9.99999999999999E+307,SEARCH($C6,Sheet2!$B$2:$B$1000),Sheet2!$G$2:$G$1000)

Does this yield what is desired? If not, care to post a scaled down sample from B of Sheet2 along with some example search values from column C of Sheet1?