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]

We have no way of knowing if the formula you are using is written correctly.
Please post data examples and the formula you are using.

 

[Will Bachrach]

=MATCH(D4,$D$39:$BD$39,0)
The value that I would like to look up is in D4. The value that I am expecting a match for is in L39.
The formula =D4=L39 returns the value "TRUE".

 

[Joe4]

It works for me. If D4 is equal to L39, it returns 9, as L39 is the ninth cell in your range $D$39:$BD$39.

Please answer ALL of the following questions:
- Are your entries in D4 and L39 numeric or text?
- Are they values in D4 and $D$39:$BD$39 hard-coded, or the result of formulas? If formulas, please post the formulas.
- What exactly is in D4 and L39 (post the actual value you are testing)?
- Are there any other cells in your range $D$39:$BD$39 that match D4? If so, what is the address of the first one (other than L39)?

 

[Will Bachrach]

-Numeric Values
-The numbers are copied and paste VALUES from formulas. The formula was "=k43+.00125". That produced the value of .095.
-.095 is the value in both D4 and L39
-No other cells match in the range

 

[RoryA]

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

 

[Joe4]

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

I thought it might be Excel's famous floating arithmetic error (see: https://support.microsoft.com/en-us...ithmetic-may-give-inaccurate-results-in-excel), but then I would suspect that =D4=L39 to return FALSE.

What do these formulas return?
=ISNUMBER(D4)
=ISNUMBER(L39)
=D4=ROUND(D4,5)
=L39=ROUND(L39,5)

Do any of those returns FALSE?

 

[Jonmo1]

Either
A) The value in D4 and/or L39 are not EXACTLY .095. But maybe they are .0948 or whatever. Cell formatting to 2 decimals makes that number 'Appear' to be .095
Formatting a number in this way does not actually change the value in the cell, it would still actuallyu be .0948. So the match would fail.
Adjust your cell formatting to show several decimals, up to 16. And see if there is a difference.

B) Issue with floating point precision errors documented here
Use Round on Both formulas
Change this
=k43+.00125
to
=ROUND(k43+.00125,5)

And change this
MATCH(D4,$D$39:$BD$39,0)
to this
MATCH(ROUND(D4,5),$D$39:$BD$39,0)

 

[Will Bachrach]

I had already checked that the numbers were exactly the same. But I tired your second approach - using the rounding function - that worked.
Thanks so much Joe4 and Jonmo1!!!
I am not sure what triggered the floating point precision error this time. I have used match functions thousands of times

 

[Jonmo1]

You're welcome.

You may not need the round on both formulas. Probably only needed on the K43+.00125 formula.