Comparing two cells

[jgrillo04]

On Sheet1 in cell A5 is 10069.

On Sheet9 in Cell A7455 is 10069.

Both are formatted as text. =EXACT(Sheet1!A5,Sheet9!A7455) returns TRUE.

Yet, =IF(Sheet1!A5=Sheet9!A7455,"T","F") returns F, and hence Index-Match is not working.

What is the problem here?

If I delete the contents of Sheet1 cell A5, then type in 10069,
=IF(Sheet1!A5=Sheet9!A7455,"T","F") returns T.

 

[Jonmo1]

Welcome to the board...

If I delete the contents of Sheet1 cell A5, then type in 10069,
=IF(Sheet1!A5=Sheet9!A7455,"T","F") returns T.

This statement tells me that you have formula's in Sheet1!A5 and/or Sheet9!A7455

I suspect this is due to a "floating point precision" issue (google it for explaination).

Try adding the ROUND function to your formula in those cells..

=ROUND(originalformula,0)


Hope that helps.

 

[jgrillo04]

Welcome to the board...



This statement tells me that you have formula's in Sheet1!A5 and/or Sheet9!A7455

I suspect this is due to a "floating point precision" issue (google it for explaination).

Try adding the ROUND function to your formula in those cells..

=ROUND(originalformula,0)


Hope that helps.

Neither Sheet1!A5 nor Sheet9!A7455 contains a formula. I am typing the functions into cell B5 (where I plan to use Index-Match) on Sheet1 (cell B5 is formatted as 'general')

 

[Jonmo1]

Ok,

If Sheet1!A5 is NOT a formula, then how did the value 10069 get into that cell before you did this..

If I delete the contents of Sheet1 cell A5, then type in 10069,
=IF(Sheet1!A5=Sheet9!A7455,"T","F") returns T.

Was it imported from some other program?


Try this on BOTH Sheet1 and Sheet9 Column A.
Copy any blank cell
Right click Column A - Paste Special - Values - Add

 

[jgrillo04]

Ok,

Try this on BOTH Sheet1 and Sheet9 Column A.
Copy any blank cell
Right click Column A - Paste Special - Values - Add

This solved my problem!

The values were imported but i've never had this problem before (atleast not to this extent).

THANK YOU

 

[Jonmo1]

Glad to help, thanks for the feedback...

Yes, "Numbers stored as text", one of the most common and most annoying issues.