Need help on searching a string in a table

[Arvind Shankar Raj]

Hello,

I am trying to search a string from tab A in a table which is in tab B.. The formula I am using is =ISNUMBER(SEARCH(B3,Table1))

However, if I use the same formula in "Tab B" next to the column where the string is actually present, this is giving me TRUE and in any other place its giving a FALSE.

I tried using the array formula as well but its not working...

Please tell me where am I going wrong.

Regards,
Arvind

 

[pecoflyer]

You cannot use the SEARCH function like that on a range of cells ( see the function's syntax)
In Sheet A you'll need something like =SUMPRODUCT(--ISNUMBER(SEARCH(B3,Table1)))
Why does it work on sheet B? Just try moving it to a row not containing your search value. It returns ...FALSE
So you are actually " cheating"
Why does it return TRUE when you enter the formula on the row containing the SEARCH string ? I'm not quite sure. As you can see the Evaluate Formula tool returns Table1 as starting from the row in which the formula is entered. Why? I don't really know

 

[Arvind Shankar Raj]

Thank you so much pecoflyer.. the sumproduct function worked... hurray

 

[pecoflyer]

Most welcome