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

43

### Join the conversation!

View:
Show:
###### Is this an additional hint?
Charles Bundy 10th Nov 2011
A1 and B1 are not the same
###### not case sensitive
damiross@... 10th Nov 2011
The IF() function is not case sensitive.
1 Vote
###### EXACT()ly Watson!
Charles Bundy 10th Nov 2011
Contributr
###### Look at row 4
ssharkins@... 11th Nov 2011
A4 and B4 both display C -- uppercase, but the formula returns FALSE.
###### I know
Charles Bundy 11th Nov 2011
Hence the smiley... Have a great weekend folks!
Charles Bundy 10th Nov 2011
1 Vote
chivite 10th Nov 2011
Like in a human relationship, you should always love the inside of a person an not only the external appearance.
###### Response
alopezlujan@... 10th Nov 2011
A and B are different. One have a space
With Med() or Len() you can obtain the value without spaces
###### First thing I thought of
damiross@... 10th Nov 2011
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)
###### When an IF() returns FALSE
wrdwzrd 10th Nov 2011
Delete the space after the C in cell B4 and you're fine
###### When and IF() returns FALSE
manieverster 10th Nov 2011
wrdwzrd, you got it. The space after the C in cell B4! Took me a while though!!
1 Vote
###### Whan an IF() returns FALSE
techrepublic@... 11th Nov 2011
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

1 Vote
###### shorter formulae
jbenton@... Updated - 15th Nov 2011
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))
1 Vote
###### TRIM alone may not be enough
david.hanshumaker@... 15th Nov 2011
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.
###### This may still not be enough
N / A 16th Nov 2011
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.
###### Sometimes
LocoLobo 11th Nov 2011
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.
1 Vote
###### So common...
peter.burtenshaw@... 14th Nov 2011
cell A4 contains "C" ; cell b4 contains "C ", hence A4B4
regrettably a common data error
###### Also, Space is not equal to Null
Ray Baker 14th Nov 2011
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.
###### Spaces, backspaces, del, unprintable ...
dold@... 14th Nov 2011
The same maladies make a vlookup() fail.
###### A bit further testing ...
nrobinson5@... Updated - 14th Nov 2011
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?
###### Auto-complete is messing with you
jody.burton@... 15th Nov 2011
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.
###### Peter is Correct
gregl@... 15th Nov 2011
In this example, A4 is the letter 'C' and nothing else. In B4 it has a space on the end. Hard to see.
###### TRUE FALSE NOT IN QUOTES
simon.freeman@... 15th Nov 2011
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.
1 Vote
###### Difference between text and value
jody.burton@... 15th Nov 2011
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.
###### @trim
jharvie673@... 15th Nov 2011
@trim fixes it
###### @?
N / A 16th Nov 2011
For me, this just results in a mailto: link.
###### A4=B4 FALSE
raicsl@... 15th Nov 2011
Charles is right. Should have used the LEN() to make sure that you are comparing apples with apples.

Les
###### I have to point out..
jmackeyiii 15th Nov 2011
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.
###### Spacing is the problem.
acsmvogel 15th Nov 2011
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.
1 Vote
###### Don't just assume
gedwards@... 15th Nov 2011
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.
Pro
Ole88 15th Nov 2011
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.
###### This could be helpful but
N / A 16th Nov 2011
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.
###### TRIM() or CLEAN()
oldbaritone Updated - 15th Nov 2011
TRIM gets both the trailing AND leading whitespace. (edit:) CLEAN gets the nonprintable characters too.
###### I'm a user, not a fixer, but...
john.elliker@... 15th Nov 2011
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().
###### My first assumption was true
wehmeier 15th Nov 2011
There is a space after the C in cell B4 therefore the two cells are not the same.
###### Extra space problem
kwilliams30@... 15th Nov 2011
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.
###### I hate extraneous white space
N / A 15th Nov 2011
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.
1 Vote
###### It is the Final Frontier...
jmackeyiii 15th Nov 2011
the irony is the letter chosen. You can not see(C) the trailing SPACE.
###### Keep it simple
Mark.Mathews 15th Nov 2011
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.
###### b4 has a space after the C
elongp 15th Nov 2011
a4 does not have a space after the C, so they're not equal
wrdwzrd 17th Nov 2011
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"
###### If you UPPER()
N / A Updated - 17th Nov 2011
or LOWER() both strings that will eliminate all case sensitivity.
###### absolute values
marj@... 4th Jan 2012
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.
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.