Formula help explanation (search function)

[Arts]

Hi All

I have the following formula =IF(SEARCH("Risk",D41),"x","no")

Where there is risk an "x" does appear so the formula is working but where there isn't the word risk instead of getting "no" I get #VALUE . Could any one assist me in getting "no" and explain why the "no" isn't being returned on my formula.

Many Thanks

Arts

 

[steve the fish]

You can use an ISNUMBER test as search will produce a number should the search term be found:

=IF(ISNUMBER(SEARCH("Risk",D41)),"x","no")

 

[Arts]

You can use an ISNUMBER test as search will produce a number should the search term be found:

=IF(ISNUMBER(SEARCH("Risk",D41)),"x","no")

Mr fish thank you for the speedy response!! Your solution works many thanks! I guess what I'm not getting is when using the below formula

=IF(AA3>=10,"Correct","under") this works fine no ISNUMBER is required for the False aspect to work

 

[steve the fish]

Because AA3>=10 produces a true or false result. SEARCH on the other hand does not so the ISNUMBER is there to produce the true/false result as required in an IF test.

 

[Arts]

I think I'm getting it this will be my last question on this, (apologies in advance if I'm irking you really trying to think like excel and understand this)

So when the ISNUMBER isn't there and and I use the SEARCH function what is the false value being thrown up by the SEARCH function for the false aspect of the formula not to work (I hope that makes sense) as I am seeing it as the word "risk" isn't there therefore that is false.

 

[steve the fish]

Well no because as i say SEARCH doesnt produce a true or false result. If you use this:

=SEARCH("Risk",D41)

you will get a value error if Risk isnt present inside the cell D41 or the position of it within the cell if it is.

 

[Arts]

Thank you so much for your patience in explaining it to me!!! It made sense seeing the result using:=SEARCH("Risk",D41) and seeing what it returned. I wouldn't have thought to use the formula with out the IF.

Thank you once again.