Microsoft

Office challenge: When an IF() returns FALSE, when it should return TRUE

This week, your challenge is to discover why a seemingly perfect IF() function returns the wrong result.

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.

42 comments
marjfahe
marjfahe

Sometimes, it's the format of the cell (general, text, etc.), so if you use the absolute value of the cell ABS(A4) compared to ABS(B4), it will match. This has been the case every time I've had this problem.

RU7
RU7

or LOWER() both strings that will eliminate all case sensitivity.

wrdwzrd
wrdwzrd

Only one thing left to solve: the "a" and "A" difference. The comments on Exact, Len, Trim etc were eye openers. What I am am surprised about however is the use of comma's instead of semicolons in most of the, otherwise right, solutions. In my version(s) of Excel that doesn't work. Hans "Wrdwzrd"

elongp
elongp

a4 does not have a space after the C, so they're not equal

Mark.Mathews
Mark.Mathews

Thanks for all the knowledge about TRIM(), CLEAN(), MED(), LEN() and whatever. I never heard of those and I'll be trying them out. However, I like to keep things simple. I use =A1=B1 to get my True or False. If it's false, in most cases, I need to review the data anyway since I'm usually comparing data that came from the same place just on different days.

jmackeyiii
jmackeyiii

the irony is the letter chosen. You can not see(C) the trailing SPACE.

RU7
RU7

I have a couple of large spread sheets, 10s of 1,000s of rows, into which I paste copied text. The authors of the sources typically include lots of unnecessary white space. So I wrote a little macro that examines each cell and deletes leading and trailing white space.

kwilliams30
kwilliams30

There is a trailing space in B4 but not in A4, which returns a FALSE statement. Yes, you can use TRIM() to fix it, but I find that to be cumbersome in a large spreadsheet. I prefer a tool that does that automatically, such as TRIM SPACES from Ablebits.com (it's free). That way the entire sheet is consistently formatted in one click.

wehmeier
wehmeier

There is a space after the C in cell B4 therefore the two cells are not the same.

john.elliker
john.elliker

I made it work by checking formating - when a row was selected, and Format Cell selected, I discovered rows were not formated. Selected "general" and everything worked, without changing spaces or doing (for me) exotic functions like len().

oldbaritone
oldbaritone

TRIM gets both the trailing AND leading whitespace. (edit:) CLEAN gets the nonprintable characters too.

Ole88
Ole88

Having dealt with a number of ways users come up with data, especially when one or more sources are from a database, it helps to add column headers so you know where it is from. Another column I would add would include the formula =IF(LEN(A1)=LEN(B1),TRUE,FALSE) This will help you see what is going on with the data itself. For the first three rows, it returns TRUE. For the last one you get FALSE. With column headers you can inspect the data source to see why the length is different and correct it if should be a single character. It can also reduce the number of "help me with this" calls you get.

gedwards
gedwards

Don't just assume and trim. Understand where the hidden character came from. It could be from a database system where "C" and "C " are truly considered unique. This can also trickle into other formulas like lookups. If it is user input, you also may want to have some kind of edit to force consistency.

acsmvogel
acsmvogel

Spacing is the problem. There is a space after the c in B4. Delete the space after the letter c and the formula returns a TRUE.

raicsl
raicsl

Charles is right. Should have used the LEN() to make sure that you are comparing apples with apples. Les

simon.freeman
simon.freeman

I was surprised to see TRUE and FALSE were not in quotation marks as I thought text in value for true and value for false had to be in them. Seems that TRUE and FALSE are exceptions to this rule.

gregl
gregl

In this example, A4 is the letter 'C' and nothing else. In B4 it has a space on the end. Hard to see.

nrobinson5
nrobinson5

I tried it with some other letters and I could only get the error with a C Also if I try entering a lowercase c in col B, it turns to uppercase - what am I doing?

dold
dold

The same maladies make a vlookup() fail.

Ray Baker
Ray Baker

I had a similar problem years ago with an IBM Series 1 computer running a program, It would fail at the same code location. On the screen it looked exactly as it should, but the problem was that one space in the command string was a Null and not a Space and the program would not run. Retyped the line and everything was Ok.

peter.burtenshaw
peter.burtenshaw

cell A4 contains "C" ; cell b4 contains "C ", hence A4B4 regrettably a common data error

LocoLobo
LocoLobo

you may want it to return false. "X" "X ". If the data is coming from a database field where you don't want to have separate entries for "X" and "X ", where they should both the "X", then this formula is pointing out the problem. If you don't care see some of the formulaes above to get around it. Personally I use the LEFT function a lot.

techrepublic
techrepublic

personally I'd change the formula to this =IF(EXACT(TRIM(A1),TRIM(B1)),TRUE,FALSE) but i've never been afraid of nesting multiple formulas :D

manieverster
manieverster

wrdwzrd, you got it. The space after the C in cell B4! Took me a while though!!

wrdwzrd
wrdwzrd

Delete the space after the C in cell B4 and you're fine

damiross
damiross

After enduring worksheets from others who import data from other programs into Excel and then matching with their own data on the sheet, my first thought (proven right) was that the "C" in B4 had a trailing space in it. When comparing text items, it's a good practice to use the trim() function: =IF(TRIM(A4)=TRIM(B4),TRUE,FALSE)

alopezlujan
alopezlujan

A and B are different. One have a space With Med() or Len() you can obtain the value without spaces

chivite
chivite

Like in a human relationship, you should always love the inside of a person an not only the external appearance.

RU7
RU7

could also give a false TRUE. For example, text with a trailing space would be the same length as the same text with a trailing non-breaking space but the text strings would not match.

jmackeyiii
jmackeyiii

they both are metaphorically apples, they are both values in an excel sheet. The point is the fly in the ointment is differing entry that seems the same. so one of the apples in our example is really a granny smith the other a crispin.

RU7
RU7

For me, this just results in a mailto: link.

jody.burton
jody.burton

You are correct about the exception. If you were to put the quotation marks around TRUE and FALSE, you would get true text as the output. As it is, the result is a logical value that displays like text.

jody.burton
jody.burton

When you type "C", Excel recognizes a previous entry "C " and suggests that. Hitting enter accepts the suggestion, in this case the capital C and the space after it, rather than just the capital C by itself.

jbenton
jbenton

the final FALSE can be omitted in IF() statements but, if all you want is a TRUE/FALSE output, you can do without the IF() altogether: =TRIM(A1)=TRIM(B1) or =EXACT(TRIM(A1),TRIM(B1))

ssharkins
ssharkins

A4 and B4 both display C -- uppercase, but the formula returns FALSE.

damiross
damiross

The IF() function is not case sensitive.

david.hanshumaker
david.hanshumaker

You never know what you might get when you download data from external sources. TRIM removes unnecessary spaces, but not non-printing characters. I'd take the extra step to say = EXACT(TRIM(CLEAN(A4)),TRIM(CLEAN(B4))) to be sure to remove any other hidden characters.

Charles Bundy
Charles Bundy

Hence the smiley... Have a great weekend folks!

RU7
RU7

There are some characters that, in the unusual case that they are present, will remain even after using these three functions. The only way to fix that is with SUBSTITUTE, replacing those characters with a character that can be handled using CLEAN, TRIM, or EXACT.

Editor's Picks