CODEWITHSUNDEEP
excelexcel-2010excel-match
user3684014
user3684014

Reputation: 1175

Strange behavior of MATCH function

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?

Upvotes: 2

Views: 319

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

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.

Upvotes: 2

Related Questions