Using IF(ISNUMBER(SEARCH

[mfabri]

Hi Guys,

I'm using the IF(ISNUMBER(SEARCH formula set to find a especific number in a excel file

=IF(ISNUMBER(SEARCH(E3,$D$2:$D$48)), E3, "NO")

Basicaly I have an array of numbers in E and a combination of words and numbers in D I need to know if the number in E is anywhere in D

For some reason the formula works with just one ROW but not the combination of 48 ROWS

Can someone help?

Thanks a lot!

 

[mfabri]

Nobody?

 

[Jonmo1]

Perhaps

=IF(COUNTIF($D$2:$D$48,"*"&E3&"*"),E3,"No")

 

[admiral100]

maybe this
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(E3,$D$2:$D$48))))>0,E3,"NO")

 

[mfabri]

maybe this
=if(sumproduct(--(isnumber(search(e3,$d$2:$d$48))))>0,e3,"no")

it worked!!!!! Can you explain why the sumproduct?????

 

[mfabri]

maybe this
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(E3,$D$2:$D$48))))>0,E3,"NO")

Last thing, this formula gives me the value in E how can I use this formula tell me in which ROW in D the E number was found?

 

[admiral100]

Also Jonmo1 formula worked! and it's more elegant

sumproduct for array , isnumber for only one value...

 

[mfabri]

This is true, Thanks both of you guys Jonmo1 admiral100

 

[Jonmo1]

how can I use this formula tell me in which ROW in D the E number was found?

Try
=MATCH("*"&E3&"*",$D$2:$D$48,0)

Note, this isn't an absolute row #.
It's an Index number relative to the searched range (D2:D48)
So if E3 is found in say D5, the formula would actually result in 4
Because D5 is the 4th item in D2:D48 (D2 is 1st, D3 is 2nd etc)

 

[mfabri]

Still can't think in how can I bring the cell to the result, now I know the number I was searching for is in the Data array, but how can I know where? So I can delet that ROW