strange #N/A error

[kizzie37]

I have a really strange error and cannot figure out why, the error onmly occurs in a half dozen cells.

I have the formula =IF(MATCH(I51,V51,0),"Match",""). it works fine for 85% of my rows, however on some I am getting this "Value not available error".

in this example the cells its being asked to match, do match both say $36.46. when I go through calculation steps, it shows that V51 value not available.... but i can see it, its there and correct I have tried copying the formulas form the working cells above and below it, no change, i have checked but cannot find any differences in either if the cells in question

Dies anyone have any ideas?

I am using Excel 2010

 

[pgc01]

Hi

It means that I51 and V51 are not equal. You are probably looking at the formatted values and it seems they are equal and they are not.

Ex. 35 and 35.0000000000001, formatted with 2 decimals will both appear as 35.00, this does not mean they are equal.

To check, format a cell as general and use

=I51-V51

You think you'd get 0, but my guess is that you won't.
Please post the result.

 

[kizzie37]

Thanks, I did that and got 0. I did check they are an exact match, the problem does not seem to be a match, it saying it can see the amount in v51, thats what is strange!!

 

[jmthompson]

Is your actual formula what you posted or did you simplify?

 

[doubledaffy]

Sorry, forgot to refresh the page before posting. Already answered

 

[kizzie37]

No formula is as posted, =IF(MATCH(I51,V51,0),"Match","").

as mentioned if i take it through "Evaluate formula" option it shows that I51 amount is picked up corrently then V51 shows #N/A, so for some reason it cannot seem to see that amount in V51, this would be fine if all of them were the same, but most of the other row with the same calc work fine, I cant seem to find out what makes these handful (spaced out throughout the sheet) different.

I assume its something to do with the calc that gives the number in V51, but again, some work fine others dont.

 

[pgc01]

Another thing that would cause the error is type mismatch.

Try in other cells

=IsNumber(I51)

and

=IsNumber(V51)

to check if they are both number values.

 

[kizzie37]

Yep, thye are both number values

 

[FKoenig]

I can't seem to recreate this error. What are the values in the cells?
Also what cell contains your formula? Are you filling up/down/sideways?

 

[FKoenig]

Also, why are you using MATCH? Why not just =IF(I51=V51,"MATCH","")?