# formula error

#### juster21

##### Well-known Member
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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Are you 100% sure the values are equal?

Are the values the result of formulas?

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?

juster21 said:
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")

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?

juster21 said:
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.

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

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

huh??

Replies
5
Views
145
Replies
2
Views
124
Replies
2
Views
419
Replies
17
Views
416
Replies
6
Views
301

1,206,971
Messages
6,075,925
Members
446,170
Latest member
zzzz02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back