Searching for string in other cells

[Ruatha]

Hi.
I have a block of cells in one part that containts text.
On rows below there are in column A text that I want the other column
in that row to search for in the above block, and then enter into tha
particular cell the text that is in column A in the row that it find
it's text in (what a gibberish... Se attachment:

I want the cell C28 to search for the content in A28 ("PS") in th
cells C11:C28 and if it finds the content, as in cell C27, write i
cell C28 the content of cell A27 "Chöl".
If it found more hits like if it looked for "OM" it would write in th
cell "Tarm, Tarm, Chöl" or "Tarm Tarm Chöl"
If it finds nothing it would write nothing.
And then I'd copy the formula to all the other cells C28,30,32,34
D28,30,32 etc...

Is there someway to do this.
COUNTIF and MATCH only works if the content matches, not if it i
among other text.

+-------------------------------------------------------------------
|Filename: excel.jpg
|Download: http://www.excelforum.com/attachment.php?postid=4862
+-------------------------------------------------------------------

 

[Pete_UK]

I can't download your attachment, but the SEARCH and FIND functions
allow you to look for sub-strings - FIND is case sensitive.

Hope this helps.

Pete

 

[Ardus Petus]

It cannot be done with plain formulas. It needs some VBA macro code.
Please zip your workbook and post the zip as attachment.

HTH

 

[Ruatha]

Well it can be done, but can it be done easier??

Right now I'd have to do like this:

Code
-------------------

=IF(ISERROR(SEARCH($A28;$C$27));"";$A27)&IF(ISERROR(SEARCH($A28;$C$26));"";","&$A$26)&IF(ISERROR(SEARCH($A28;$C$25));"";","&$A$25)

-------------------

And repeat that 17 times instead of 3, or 17x2 if I want the D colum
aswell..

Is there some easier way to do it as a function?