search a cell for values in a range, return values found

I have a list of values in column A on sheet1. Something like this:

A1=" AM100 "
A2=" RM100 "
A3=" URM100 "
etc.

I need to search inside of another cell that contains a comma delimited list in C1 on sheet2, for instance:

C1=" AM100 , MEO1 , RM100 , TXT1 "

I am trying to find a way to output all of the values that exist in column A and are found in cell C1 in a comma delimited string in another cell. For instance, output should be D1 on sheet2:

D1=" AM100 , RM100 "

I have been banging my head against this and cannot figure it out.

Have a look at this thread with very similar requirement:

http://www.excelforum.com/excel-work...arguments.html

add the same UDF to your spreadsheet and then use similar formula:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(SEARCH(" "&$A$1:$A$3&" "," "&C1&" ")),$A$1:$A$3,"")," "))," ",", ")

confirmed with CTRL+SHIFT+ENTER not just ENTER

Seems I late)) But I'll post code anyway))

Please Login or Register  to view this content.

Or this UDF

Please Login or Register  to view this content.

Then in D1 of second sheet use this formula
=SrchValInRng(Sheet1!A1:A3,C1)

HTH))