Match Function Returning "N/A" even though there is a match

[Will Bachrach]

I am using a simple match function to find the position of a number. Although the numbers are equal (they are numbers less then 1) the match function still returns "N/A". I even checked to if their values are equal by using the formula "=A1=A2" where the two numbers are in those two cells, and Excel returns "TRUE". So why isn't the Match function helping? Can someone please advise!

 

[Joe4]

I have used match functions thousands of times

Yes, it isn't always predictable when it would show up.

I just find it baffling that =D4=L39 was returning TRUE in that instance.
Usually, when you have that issue, it will return FALSE, even though when it looks like they are equal.

 

[Jonmo1]

I just find it baffling that =D4=L39 was returning TRUE in that instance.

Me too, but I have seen it recently in another forum.
If the 2 cells achieve the values with different formulas.
Particularly, one recieved it's value with an index type, while the other used a small(if array type.
They were both the same value, yet match failed.

 

[Joe4]

Particularly, one recieved it's value with an index type, while the other used a small(if array type.
They were both the same value, yet match failed.

Interesting...
Good to know.

 

[Will Bachrach]

Right. I only did it to the K43+.00125 fromula.

 

[joeu2004]

It's almost certainly a rounding difference between the stored/displayed number in one cell versus the other.

That does not make any sense to me. If =D4=L39 returns TRUE, I don't see how your formula could return #N/A.

Joe4 is correct: it does not make any sense. But RoryA is correct insofar as: the nonsensical behavior is due to the inconsistent way that Excel does comparisons, in a half-baked attempt to hide ("correct") anomalies that arise due to the binary floating-point representation.

Consider the following example:

A1: 91.607
A2: 74.178
A3 (17.429): =A1-A2
A4 (17.429): =ROUND(A1-A2,15)
A5 (TRUE): =A3=A4
A6 (#N/A): =MATCH(A3,A4,0)
A7 (3.55E-15): =A3-ROUND(A3,15)-0

A5 returns TRUE because in Excel, the "=" operator compares left and right values rounded to 15 significant digits.

A6 return #N/A because MATCH compares the exact binary representations, which might differ from the values rounded to 15 significant digits.

In fact, A7 demonstrates that A3 differs from the value rounded to 15 significant digits by about 3.55E-15.

FYI, the "redundant" -0 in the formula in A7 works around another instance of Excel's half-baked attempt to hide binary floating-point arithmetic anomalies. In this case, Excel arbitrarily substitutes exactly zero (0.00E+0) for the infinitesimal difference A3-ROUND(A3,3) because Excel deems A3 and ROUND(A3,15) to be sufficiently relatively "close".

These half-baked heuristics are alluded to, but poorly and incompletely described, in KB 78113 (click here) [1] under the misleading title "when a value reaches zero". The heuristics are inconsistently applied in Excel.


-----
[1] http://support.microsoft.com/kb/78113

 

[joeu2004]

PS....

A7 (3.55E-15): =A3-ROUND(A3,15)-0
[....]
FYI, the "redundant" -0 in the formula in A7 works around another instance of Excel's half-baked attempt to hide binary floating-point arithmetic anomalies. In this case, Excel arbitrarily substitutes exactly zero (0.00E+0) for the infinitesimal difference A3-ROUND(A3,3) [sic] because Excel deems A3 and ROUND(A3,15) to be sufficiently relatively "close".

I should have written =A3-ROUND(A3,15), because it is only when they are the last two terms of a formula that Excel invokes the heuristic to substitute exact zero.

For example, Excel does not make the substitution in the following context: =IF(3-ROUND(A3,15)=0,TRUE). That is why that returns FALSE, but =A3-ROUND(A3,15) displays exact zero (0.00E+0).

Again, "it does not make any sense", as Joe4 said, which is my definition of a design flaw.