How to populate Sheet2 from Sheet1 after finding matching word on Sheet2 from list in Sheet1?

[nbwoodruff]

I have 2 Worksheets in an Excel 2010 Workbook -

Sheet1
Column1: contains the word "dog"
Column2: contains the word "bark"

Sheet2
Column1: contains the sentence "I like dogs a lot."
Column2: is blank

What I need to do is search Sheet2/Column1 for the presence of "dog" and if it's present, populate the word "bark" in Sheet2/Column2 from Sheet1/Column2.

How can I do this?

Any help is appreciated!

Thank you!

NBW

 

[gehusi]

Enter this formula in Sheet2 B1 (assuming the phrase "I like dogs a lot" is in A1 on Sheet2).

=IF(ISERROR(SEARCH("dog",A1)),"",VLOOKUP("dog",Sheet1!A:B,2,0))

If there is a lot of data, or you will be looking up many phrases in column A of Sheet2, then you may want to substitute INDEX+MATCH for VLOOKUP to improve performance.

 

[nbwoodruff]

Awesome, thanks! Will give it a try and report back. Only about 1,000 rows of data - so not too big - but slowly evolving and growing...

 

[nbwoodruff]

Gehusi - ok, I can get that to work, thank you very much for this. Is there a way to make the 2 "dog" entries you have in the formal dynamic? Meaning, the word "dog" wouldnt be in the formula at and instead would be a range of cells for both instances of the word "dog"? Thanks again, Nathan

 

[gehusi]

A range of cells, no - that would require a bit more heavy lifting. A single cell? Easy enough - just replace "dog" with the cell reference containing the text you want to match. For example, if the text is in cell G1:

=IF(ISERROR(SEARCH(G1,D1)),"",VLOOKUP(G1,Sheet1!A:B,2,0))