formula error

[juster21]

this is my formula...

=if(C1=SHEET2!B1,SHEET2!A1,"NOT EQUAL")

This works fine until row 121.....can't figure out why. The values are equal.

 

[Norie]

Are you 100% sure the values are equal?

Are the values the result of formulas?

 

[NBVC]

Are there spaces before or after any of the cell entries. Are the formats the same (i.e. perhaps one is text, the other is number)?

What is the result you are getting?

 

[Aladin Akyurek]

this is my formula...

=if(C1=SHEET2!B1,SHEET2!A1,"NOT EQUAL")

This works fine until row 121.....can't figure out why. The values are equal.

If you work with calculated numbers, try to round before testing for equality:

=IF(ROUND(C1-SHEET2!B1,2)=0,SHEET2!A1,"NOT EQUAL")

 

[juster21]

Interesting!! My C value has a space before the first number but I can't see that space. If I click in the formula bar and backspace, the number doesn't move but the formula does work. Any way to fix this on a BIG scale?

 

[Aladin Akyurek]

Interesting!! My C value has a space before the first number but I can't see that space. If I click in the formula bar and backspace, the number doesn't move but the formula does work. Any way to fix this on a BIG scale?

Try ASAP Utilities or Edit|Replace.

 

[NBVC]

Not sure if I am understanding you, but maybe try to envelope your C1 with Trim.

i.e. =If(Trim(C1)=...

 

[juster21]

huh??