Excel compare 2 cells...

This is so unbelievable in comparing 2 cells not working.  I have tried =A1=O1, EXACTMATCH, COUNTBLANK and a number of other IF statements and none work.

It is super simple what I am trying to do.

Compare 2 cells that may or may not have null values.  If the text is identical, give me a "O" else give me an "X".

So if the cells are both null or both exact text matches I want a "O", otherwise an "X".

PLEASE HELP lol....I have been doing this for hours and nothing is working (shouldn't this be easy?)  :)
LVL 1
cyimxtckAsked:
Who is Participating?
 
Steven CarnahanNetwork ManagerCommented:
Have I missed something in your request?  This is what I get as a result:

eecaptuer.jpg
Using Excel 2007, formula in C1 and copied down through C6
0
 
oleggoldCommented:
i'd try search, also You may have blanks so You'll need to trim the values in both cells.
0
 
Steven CarnahanNetwork ManagerCommented:
=IF(A1=O1,"O","X")
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
cyimxtckAuthor Commented:
=IF(A1=O1,"O","X") doesn't work.  It evaluates to O for all values regardless if they are different or not.
0
 
cyimxtckAuthor Commented:
null and text is a case in point.  A1 = null and O1 has text
0
 
NorieVBA ExpertCommented:
Strange if I try this with A1 empty (null) and O1 with text the result is X.

=IF(A1=O1, "O", "X")

What exactly do you have in A1 and O1?
0
 
Steven CarnahanNetwork ManagerCommented:
If I put a space (blank character) in A7 and nothing in cell B7 then I get an X because they don't equal even though they look like they do.  That is the only option that I can think of that would cause this.
0
 
cyimxtckAuthor Commented:
MS Office Professional Plus 2010 here is what I get:

IF(A200=O200, "O", "X")

A200 = nothing
O200 = Interactive Data

X works and finds the correct answer.

IF(A203=O203, "O", "X")

Fails and gives me an X when both cells are blank.

IF(TRIM(A201)=TRIM(O201), "O", "X")

A201 = nothing
O201 = Spotfire

Fails and gives me a O.

See what I mean? this should be so simple but things are not working.  Is there a setting somewhere or something?
0
 
cyimxtckAuthor Commented:
Something is jacked with that other spreadsheet....I did the same test on my laptop and your above logic works....NO clue how that is possible but I have been pulling out what little hair I have left for no reason.

Maybe it is corrupt?

Thanks for the help and sanity test!
0
 
Steven CarnahanNetwork ManagerCommented:
Thanks for letting us know.  I was starting to pull out what's left of my own hair.  I couldn't get it to fail and I have been trying all sorts of ways.  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.