Handling #VALUE?

[(PeteCresswell)]

Sample Code:
------------------------------------------
=IF(SEARCH("Taxable",RC[3])=1, TRUE,FALSE)
------------------------------------------

If "Taxable" is found, no prob: result= "True".

OTOH, if "Taxable" not found, SEARCH (just like
the documentation says....) returns #VALUE.


Question: How do I trap for #VALUE in the IF() statement
and convert it to "FALSE"

 

[PeteCresswell]

Question: How do I trap for #VALUE

I'm thinking it's something with ERROR.TYPE, but I can't make it work.

e.g.
=IF(ERROR.TYPE((SEARCH("Taxable",RC[3]))=3),FALSE,IF(SEARCH("Taxable",RC[3])=1,
TRUE,FALSE))

Returns "FALSE" when it should, but returns #NA instead of TRUE when
the string is found.

 

[Chip Pearson]

The ERROR.TYPE function will fail if it is passed anything that is not an
error. Therefore, you must use ISERROR to first test whether a cell has an
error and then, if so, call ERROR.TYPE to find the type of error. E.g.,

=IF(ISERROR(A1),ERROR.TYPE(A1),"No Error")


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

Question: How do I trap for #VALUE

I'm thinking it's something with ERROR.TYPE, but I can't make it work.

e.g.
=IF(ERROR.TYPE((SEARCH("Taxable",RC[3]))=3),FALSE,IF(SEARCH("Taxable",RC[3])=1,
TRUE,FALSE))

Returns "FALSE" when it should, but returns #NA instead of TRUE when
the string is found.

 

[PeteCresswell]

The ERROR.TYPE function will fail if it is passed anything that is not an
error. Therefore, you must use ISERROR to first test whether a cell has an
error and then, if so, call ERROR.TYPE to find the type of error. E.g.,

=IF(ISERROR(A1),ERROR.TYPE(A1),"No Error")

That did it:

=IF(ISERROR(IF(SEARCH("Taxable",RC[3])=1,
TRUE,FALSE)),FALSE,IF(SEARCH("Taxable",RC[3])=1, TRUE,FALSE))

Thanks!