Search a cell for a specific text

[williamkwells]

Good morning.

What I am trying to do is test a cell condition for specific text content based on a range.

=SUMPRODUCT(--ISNUMBER(SEARCH(THINGS,A34)))>0

I found this formula which test the cell based on the contents in the range THINGS. It returns TRUE or FALSE.

What I want to know is which item in THINGS did it find, if TRUE which item did it find instead of just TRUE or FALSE.

Thank you in advance.

William

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[williamkwells]

Good morning.

What I am trying to do is test a cell condition for specific text content based on a range.

Please Login or Register  to view this content.

I found this formula which test the cell based on the contents in the range THINGS. It returns TRUE or FALSE.

What I want to know is which item in THINGS did it find, if TRUE which item did it find instead of just TRUE or FALSE.

Thank you in advance.

William

[Fluff13]

How about
=IFERROR(LOOKUP(10^5,SEARCH(THINGS,A5),THINGS),"")

If you want complete matches use
=IFERROR(LOOKUP(10^5,SEARCH(THINGS&" ",A5),THINGS),"")

[Fluff13]

Another option is
=FILTER(THINGS,ISNUMBER(SEARCH(THINGS&" ",A5)),"")

But you will need to remove the 2nd UCE from the Named range & reduce the range to J2:J8

[williamkwells]

Thank you so much for the response

Please Login or Register  to view this content.

worked the others did not

In order to get to work properly, I had to add a space in front & back of each of the THINGS

William

[Fluff13]

You're welcome & thanks for the feedback