Microsoft

Excel troubleshooting challenge: Why is C4 returning FALSE?

Can you figure out why why a seemingly perfect IF() function returns the wrong result?

This challenge was originally published in November 2011, and remains one of the most popular challenges we ever published.

A user sends you a workbook with a simple IF() function that compares the text in two cells. If the two values are the same, the IF() function returns TRUE. When they don't match, the IF() function returns FALSE. Simple, right? It should be, but there's a fly in this one. Two cells (A4 and B4) contain the same text value, but the IF() function returns FALSE. Any idea what the culprit is?

There's more than one possibility, so we made the downloadable example available.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
fkuypers
fkuypers

Because of leading or trailing blanks I always encase my tests in TRIM, as in IF(TRIM(A4)=TRIM(B4),TRUE,FALSE), especially since my data is quite often sourced from some other file or process.


Eric the Trainer
Eric the Trainer

The value in B4 isn't the same as A4. A4 has the value C(no spaces) and B4 has the value C (with one space after) and therefore the IF() Function correctly returns FALSE.

The culprit is our eyes, we can't see the extra space following C in cell B4.  Some would argue the culprit is whoever put that silly space next to C in B4, but I'm not the type to point fingers. :)

Editor's Picks