#VALUE! error

[ericl770]

i'm using the "search" function within a "if" function to return a value from a column.

=if(search("co";E6);G6;0)

Meaning : if the caracters "co" (not case-sensitive) is included in the cell E6, return the value from G6, if not return 0 (zero).


When it's true, it does return the value from G6. But when it's false, it says "#VALUE!"

I've tried putting all columns involve in same format, but still does not work !

Please help me !

 

[adam087]

It's because SEARCH returns the #VALUE error when it doesn't find the search string.

Try
=IF(NOT(ISERROR(SEARCH("co", E6))), G6, 0)

Regards
Adam

 

[VoG]

Try

=IF(ISNUMBER(SEARCH("co";E6));G6;0)

 

[andiemac]

When a search does not find the criteria it returns an error, not a true or false, so the "if" formula doesn't know what to return. Try turning it around like this:

=IF(ISERROR(SEARCH("co",E6)),0,G6)

 

[ilya2004]

I have never seen the semicolon used in a formula before, why are you using that instead of a comma?

 

[ericl770]

COOL !

the "=if(not(iserror(search....)))" thing works !

Don't know why yet, but i will try to understand those functions !

Thanks guys for VERY VERY prompt response ! This forum is amazinG

Thanks again everyone !!!


Eric

 

[ericl770]

The semicolon is because my computer is set on a FRENCH regional language format.

 

[ericl770]

The "=if(isnumber(Search)))... function also works !

Thanks alot