Discussion on:

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

A1 and B1 are not the same

The IF() function is not case sensitive.

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

LEN() is your friend...

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

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

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)

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

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

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

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))

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.

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.

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

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.

The same maladies make a vlookup() fail.

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?

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

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.

@trim fixes it

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

Les

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.

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.

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.

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

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().

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

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 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.

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

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.

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

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"

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

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.