IF with multiple SEARCH gives #VALUE

[athegn]

This is a follow on from “Err504 in If formula”

Text Formula Result

Blah Blah Circular Blah =IF(SEARCH(“Circular”,B4),“Circular”,IF(SEARCH(“Linear”,B4),“Linear”,"")) Circular

Blah Linear Blah Blah =IF(SEARCH(“Circular”,B5),“Circular”,IF(SEARCH(“Linear”,B5),“Linear”,"")) #VALUE

Blah Blah Blah Blah Blah =IF(SEARCH(“Circular”,B6),“Circular”,IF(SEARCH(“Linear”,B6),“Linear”,"")) #VALUE

The first #VALUE should be Linear and the second #VALUE should be “”

Why do I get #VALUE?

Any advice please?

[PKG]

The SEARCH function returns a number.
However, the IF function tests for TRUE or FALSE.
So you have to extend the formula to make that happen.
This works e.g. with the ISNUMBER function.

=IF(ISNUMBER(SEARCH("Circular",B4)),"Circular",IF(ISNUMBER(SEARCH("linear",B4)),"Linear",""))

[athegn]

Thank You

You just beat me to typing my answer. Found it on an Excel website.