Search text string for range of text values - return match

[crugg]

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!

[mojo249]

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

[gmahlert]

I wrote a custom function which works for the example that you gave.

Please Login or Register  to view this content.

Although, I can't seem to figure out how to do it without using On Error resume nextAttachment 80558

[Ron Coderre]

Using your posted example...
on Sheet2, this regular formula searches the Sheet1 cell for the Sheet2 values and sums the corresponding amounts for each match:

Please Login or Register  to view this content.

In your example, the formula returns: 30

If your Sheet1 text is: The dog cannot help the car
the formula returns: 100

Is that something you can work with?

[crugg]

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?

[crugg]

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.

[crugg]

gmahlert - works perfectly. Thanks much!!