Search from string to lookup table

I'm trying to conceptualize a formula and am hoping that there is a simple solution that I'm not seeing. I have searched around and can't find a soltion on the site based on my search criteria...(I may well be using the wrong criteria) Any advice would be GREATLY appreciated.

This is an overly simplified version of what I'm trying to accomplish.

It would involve a list of fruits on one sheet and sentences on another sheet. I want to find the fruit from the first sheet in the sentence on the second sheet and retun the fruit type to the second sheet to the next cell.

Sheet 1
A
1 Apple
2 Crabapple
3 Mango
4 Pear
5 Papaya

Sheet 2
A B
1 The apple does not fall far from the tree. (returns) Apple
2 It is not time to eat the Pear. (returns) Pear

I'm trying to figure out how to write the formula to return the Column B on Sheet 2.

I've considered VLOOKUP, Search, Index and Match functions but can't seem to pull it out of my brain as to how to do it because the value I need to search for is nested in the text string of the sentence. Thoughts?

sorry for the Columns and rows not lining up...they lined up perfectly when I was constructing the post.

Originally Posted by olsoniv

sorry for the Columns and rows not lining up...they lined up perfectly when I was constructing the post.

That is why we suggest adding a sample sheet

Sorry about that....I've attached one.

Thanks!

wow! Super elegant! Thank you! Even worked in my real world example with over 2000 "fruit" entries!

Interesting note: I didn't have to hit Ctrl-Shift-enter in Excel 2010...

So sorry to post another question on this thread....This works except with one problem. In my real world example, crabapple would be 2 words, not one. Ergo it finds two instances of "apple" and adds them together and returns the row for "Mango" instead. Is there a way to work around that? any thoughts would be awesome....thanks.

I've attached a new sheet with the updated example.

needed to do another post to subscribe to my thread...

any ideas out there for me? Pretty please?

Wow...that is quite the formula and looks like it works! I REALLY appreciate the help. I will take some time and read up on each of the functions used to understand what exactly it is doing.

Weather is great here! Had a really nice warm fall...was in the 60's today and the mountains are getting snow..best of both worlds!

Thanks again!

PS...the more I look at that formula, the more I realize how far outside my pay grade it is! Just when I think I know a couple things about Excel...I see something like this to realize I don't really know that much. Thanks again!