Explanation
By default, Excel is not case-sensitive. For example, with “APPLE” in A1, and “apple” in A2, the following formula will return TRUE:
=A1=A2 // returns TRUE
To compare text strings in a case-sensitive way, you can use the EXACT function . The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not.
If we use EXACT to compare A1 and A2 as above, the result is FALSE:
=EXACT(A1,A2) // returns FALSE
EXACT with IF
You can use this result inside the IF function to display a message or make a conditional calculation. For example, to display the message “Yes” for a match and “No” if not, you can use a formula like this:
=IF(EXACT(A2,A2),"Yes","No")
Explanation
This formula uses boolean logic to output a conditional message. If the value in column C is less than 100, the formula returns “low”. If not, the formula returns an empty string ("").
Boolean logic is a technique of handling TRUE and FALSE values like 1 and 0. In cell C5, the formula is evaluated like this:
=REPT("low",C5<100)
=REPT("low",TRUE)
=REPT("low",1)
="low"
In other words, if C5 < 100, output “low” 1 time. In cell C6, the formula is evaluated like this:
=REPT("low",C6<100)
=REPT("low",FALSE)
=REPT("low",0)
=""
In other words, if C6 < 100 is FALSE, output “low” zero times.
IF function alternative
Conditional messages like this are more commonly handled with the IF function. With IF, the equivalent formula is:
=IF(C5<100,"low","")
Both formulas return exactly the same result, but the REPT version is a bit simpler.
Extending the logic
Boolean logic can be extended with simple math operations to handle more complex scenarios. Briefly, AND logic can be expressed with multiplication (*) OR logic can be expressed with addition (+). For example, to return “low” only when (count < 100) AND (day = Monday) we can use boolean logic like this:
=REPT("low",(C5<100)*(B5="Monday"))
The equivalent IF formula is:
=IF(C5<100,IF(B5="Monday","low",""),"")
or, simplifying a bit with AND:
=IF(AND(C5<100,B5="Monday"),"low","")
Coercing TRUE and FALSE to 1 and zero
When using boolean logic, you’ll sometimes need to force Excel to coerce TRUE and FALSE to 1 and zero. A simple way to do this is to use a double-negative (–).
