• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting around the #VALUE! error returned by the SEARCH( )

In the attached file, my formulas are in the yellow cells. For each row, I want to return a statement indicating the proper Revenue Formula that should be used which is contingent upon the Contract Type.

In cell D7 I get the correct answer, but when I copy down I get the value error.

This appears to be due to the fact that the first SEARCH( ) is returning a #VALUE! error and because of this, the entire formula returns an error, even though the syntax for the nested IF would return a correct result on its own (see cell J8). So it's as if Excel isn't getting to the 2nd IF statement.

To solve this, I have to get Excel to ignore the first SEARCH( ) if that fails. I tried to use the IFERROR( ). I applied the entire formula inside of IFERROR( ) but that didn't work. I applied IFERROR( ) to the first SEARCH( ) and couldn't get that to work.

Is there any way to solve this without the IFERROR( )? If not, then what portion of the formula should the IFERROR( ) be applied to?
 

Attachments

  • SEARCH function returns VALUE error.xlsx
    11.1 KB · Views: 5
Hi,

Replace part of your formula from SEARCH("CPFF",C7)>0 into ISNUMBER(SEARCH("CPFF",C7))

So, D7 formula become >>

=IF(ISNUMBER(SEARCH("CPFF",C7)),"Revenue formula is CPFF",IF(ISNUMBER(SEARCH("FFP",C7)),"Revenue formula is FFP"))

Or,

you can use this shorter Lookup+Search formula instead

="Revenue formula is "&LOOKUP(1,-SEARCH({"ffp","cpff"},C7),{"FFP","CPFF"})

Regards
 
Last edited by a moderator:
Hi,

Replace part of your formula from SEARCH("CPFF",C7)>0 into ISNUMBER(SEARCH("CPFF",C7))

So, D7 formula become >>

=IF(ISNUMBER(SEARCH("CPFF",C7)),"Revenue formula is CPFF",IF(ISNUMBER(SEARCH("FFP",C7)),"Revenue formula is FFP"))

Or,

you can use this shorter Lookup+Search formula instead

="Revenue formula is "&LOOKUP(1,-SEARCH({"ffp","cpff"},C7),{"FFP","CPFF"})

Regards

This is great. This worked.
 
Back
Top