Is there a way in which i can search a particular substring in all rows of a column in excel

Question

I have used the following code, but it does not work

ISNUMBER(FIND(B2,$A$2:$A$6$7)).

Please guide.

Thanks

Answer 1

Use MATCH and wildcards:

=ISNUMBER(MATCH("*"&B2&"*",$A$2:$A$67,0))

to return the value we use INDEX:

=INDEX($A$2:$A$67,MATCH("*"&B2&"*",$A$2:$A$67,0))