Search text string for range of text values - return match

Hello - here is what I'm trying to do:

- Cell A1 on sheet 1 contains a text string (ie. "I need help with an excel function")

Range A1:B4 on sheet 2 looks like:

A B
1 The 10
2 Car 20
3 Help 30
4 Dog 40

etc ....

I need a formula in sheet 1 that looks at the string in A1 and compares it to the range in Sheet 2. If one of the words in Sheet 2 are contained in the Sheet 1 text string, return the numerical value to the right of the matching word in Sheet 2.

So, in my example above, the formula would return "30".

Alternatively, if this is not possible, I could work with the formula returning the matching word only (ie. "help" in the example above)

Thanks!

It's possible, though not with a formula. You'd have to use vba.

It would also make it easier if you could separate the word (Help) from the value (30). Is it possible to have the word in column B and the value in column C?

Dion

Ron - thanks for your reply. Yes, I could work with this but your formula does not seem to work when I try it. I dont see how this formula will search the text string in Sheet 1 for the individual text in Sheet 2.

Thoughts?

Dion - yes it is separate. The format of my original post didnt come out as I'd typed it. Column A is the text. Column B is the corresponding value.

gmahlert - works perfectly. Thanks much!!