2

In Excel 2010, Windows 7, I encounter following strange behavior of MATCH function.

In A1, type in 0.81, and select A1:B1, click Fill->Series, in step value box enter 0.01, then we have 0.82 in B1.

In A2, type in 0.82, and in B2, enter formula:

=MATCH(A2,A1:B1,0)

Then we would have #N/A error, the whole thing looks like this:

0.81    0.82
0.82    #N/A

But when I hand-type 0.82 in B1, everything works fine, is it a bug or these two number are different in some sense? I tried

TYPE(B1)=TYPE(A2) and B1=A2

They both return TRUE, what formula can show that they are different?

Ram
  • 3,092
  • 10
  • 40
  • 56
user3684014
  • 1,175
  • 12
  • 26

1 Answers1

2

Great question

This is a matter of precision...........even though B1 appears to be an exact match to A2, it is not. There are close, VERY close; so close that even =A2=B1 will return True.

But they are not close enough to MATCH() to work.

EDIT#1:

To examine the difference between B1 and A2 run this VBA macro:

Sub dural()
    Dim A2 As Range, B1 As Range
    Set A2 = Range("A2")
    Set B1 = Range("B1")
    v1 = A2.Value
    v2 = B1.Value
    MsgBox (v1 = v2) & vbCrLf & v1 - v2
End Sub

You should see:

-1.11022302462516E-16

Without VBA, the =DELTA() function will also reveal them to be un-equal.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thanks, I didn't notice this might be the reason. But if I change the number to 0.82 and 0.83, it works fine again, so the difference of error tolerance between "=" and MATCH() should be small, where can I find such documentation? – user3684014 Jul 02 '14 at 16:15
  • 1
    http://support.microsoft.com/kb/315961 for HLOOKUP and VLOOKUP, but applies to MATCH too. – Dick Kusleika Jul 02 '14 at 17:54