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:

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

Originally Posted by Fluff13

How about

Formula:

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

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.

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.

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

You are welcome.