View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
PaulW PaulW is offline
external usenet poster
 
Posts: 130
Default Match doesn't work all the time?

In column A I have a list of numbers from 1 to 300.
In column B I have =100/A1 dragged down.
In column C I have = ROUND(B1,0)/100

In column E I have numbers starting at 0.01, and increasing by 0.01.
In column F I have =MATCH(A1,$C$1:$C$300,0)

Some of these are errors, I don't have the first clue why.
0.05 gives the answer 19, so on row 19 I can see that, 100/19 is 5.26316, I
can see that this is rounded to 5, then when divided by 100 is now 0.05, and
this if the first instance of 0.05 in column C. Thats perfect.
0.06 gives an error though. But I can see that on row 16, 100/16 = 6.25000,
which when rounded and divided by 100 is now 0.06. There are 3 rows where the
answer is 0.06 (16, 17, 18) but match can't seem to find any of them?
Similarly, I can see that there are errors for 0.10 to 0.16, where I can
clearly see in column C that the numbers 0.14, 0.13, 0.11 and 0.10 are
present.

I tried changing it to a vlookup (although I need it as a match) and the
errors were still aparantly. I've tried changing the match type in the
formula to -1, this give me an answer for each one, but quite often the wrong
answer...

I've done a formula of =E6=C16 to see if both 0.06's are the same, and it
returns as TRUE, so why can't MATCH find it?