Search a text for a array of values

Hello,

I have a array of values, i need to search a text if any of the array of values exist in it.

I used below, it works fine but if there is a text like "1. Ship(R45100A)" and both R45100 & R45100A exist in array, it shows result as R45100. but expectation is to get R45100A

=INDEX(Objects!A:A,AGGREGATE(15,6,ROW(Objects!A$2:A$50)/(ISNUMBER(SEARCH(Objects!A$2:A$50,A2))),1))

OR is there a way to get the count of values in array found in text?

PFA data, please let me know if any thoughts

Thanks
Amith

How about

Formula:

Please Login or Register  to view this content.

Originally Posted by Fluff13

How about

Formula:

Please Login or Register  to view this content.

Thank you for your response, it is working great. but we cannot relay on ")" as it may sometimes does not exist in text.
any other option? OR is there a way to get count of values in array found in text?

In that case please upload a file with more realistic data.

B2

=IFERROR(INDEX(Objects!A:A,MOD(AGGREGATE(14,6,(ROW(Objects!A$2:A$50)+LEN(Objects!A$2:A$50)*10^4)/((Objects!A$2:A$50<>"")*ISNUMBER(SEARCH(Objects!A$2:A$50,A2))),1),10^4)),"")

copied down.

Originally Posted by windknife

B2

=IFERROR(INDEX(Objects!A:A,MOD(AGGREGATE(14,6,(ROW(Objects!A$2:A$50)+LEN(Objects!A$2:A$50)*10^4)/((Objects!A$2:A$50<>"")*ISNUMBER(SEARCH(Objects!A$2:A$50,A2))),1),10^4)),"")

copied down.

Thank you so much, this is working fine

Originally Posted by Fluff13

In that case please upload a file with more realistic data.

Sorry for that, will keep this in mind for next time.

You are welcome.