How to find and return value

This is what I'm trying to do:
I need to find C1 within a text string C1, C2, C3 and return the value on the next cell in an array. The problem is that the lookup functions do not recognize C1 within the C1, C2, C3 string. Other functions like Find or Search will find the C1 within the C1, C2, C3 but they can not be used in arrays
Can anyone help me with this one
Thank you in advance
Marta

Hello Lecxe,
Thank you very much for your help
it really works!
Please see attach file
Formula in B2 cell works using your suggestion, however, formula in B3 cell does not work, which is if I want to look for the value in another cell instead of a value itself
Thank you again
Marta

You have to use absolute reference for the lookuparea. This means putting $ symbol in the cell reference. Then you can copy the formula to the other cells.


Put this formula in B1 and copy down

Please Login or Register  to view this content.

Bjornar and Lecxe,
Thank you very much for your help
You have no idea of how much time you have saved me
I missed the $ in the sheet sample I sent, but I always do that. What I had missing was
"*"&A1&"*"
Thank you again, I really appreciate your help
Marta