MATCH Function inconsistent in return value

I am using Excel 2002 and trying to do something which should be simple with MATCH.

I have an array of numbers ordered, starting at 0 and incrementing by 0.05 up to 20. I am then using the MATCH function to find the relative position of numbers 0,1,2,3,4 and 5. However, for number 0,1 and 2 the return value is one less than what it should be, for the numbers 3, 4 and 5 it is working as expected. By the way, the match_type I am using is the default 1.

Also I have formatted everything to be numbers and tried various things already, so am beginning to wonder is this is a known issue. I really hope not.

Sorry, I am a newbie and joined about 5 minutes ago.

Okay so in in first A column I have my numbers 0, 0.05, 0.1, 0.15 upto 24.95, so lets call this range A1:A500 and call it myRange.

Then in say C column, then I do MATCH 5 times...

MATCH(0, myRange, 1) = 1 (correct)
MATCH(1, myRange, 1) = 20 (wrong)
MATCH(2, myRange, 1) = 40 (wrong)
MATCH(3, myRange, 1) = 61 (correct)
MATCH(4, myRange, 1) = 81 (correct)
MATCH(5, myRange, 1) = 101 (correct)

So real question is, why does it always get the second and third one listed above wrong?

works ok for me ,see attached put number in b1, could be a formatting problem?

Thank you for your replies.

I have to increment the numbers dynamically by 0.05 rather than have them fixed. When I do this, I see the error.

I have uploaded an example.

Because column A generate number with many digit.

You can check by enter =Len(a1) in B1 > Copy down and see results.

To solve this problem

A2

=ROUND(A1+0.05,2)

Enter and copy down.