When you wished that Excel had short-circuit boolean evaluation: Excel IF, OR and SEARCH function combination
Posted by jpluimers on 2012/09/04
I had a function like this in Excel:
=IF(OR(B2=""; SEARCH(C2;B2)); "ok"; "INCONSISTENT")
The situation should be OK when either:
- Cell
B2is empty (an empty cell is considered equal to a zero length string). - One or more occurrences of the value in cell
C2can be found in the value of cellB2
Since most of my software development is outside of Excel, I’m used to short-circuit boolean evaluation.
Not with Excel: the OR is a function, not an operator, so all other function calls will be evaluated.
Since SEARCH will return #VALUE! when the first argument does not have a value, and #VALUE! propagates in a similar way as NULL in SQL does (SQL has the Boolean datatype inconsistency though).
So you need to get rid of #VALUE! as soon as you can using the one of the IS functions like the ISERROR function or the ISNUMBER function.
ISNUMBER, contrary to popular belief, not only distinguishes numbers from text, but in fact from any non-numeric value as Glaswegian kindly explained on the TechSupport forum:
Excel IF and SEARCH function combination
I am trying to do the following:
If cell A1 contains the characters “ABC” anywhere in the string or value, then “Y”, else “N”. I almost have it by using =if((search(“ABC”,A1)),”Y”,”N”). However, with the “else” if “ABC” is not found, it returns #VALUE! as opposed to “N”.
[…]
Try
=IF(ISNUMBER(SEARCH("abc",A1,1)),"Y","N")ISNUMBER checks the type of value and returns TRUE or FALSE. The #VALUE error refers to the fact that Excel could not translate the value to the type required.
So the code ends up as this:
=IF(OR(B2=""; ISNUMBER(SEARCH(C2;B2))); "ok"; "INCONSISTENT")
–jeroen
via: Excel IF and SEARCH function combination – Tech Support Forum.
Leave a Reply