Match Returning #N/A When Item Exists

[TonyMJ]

I use Match (& Index) a lot but this one has me stumped.

My match returns #N/A even though the value definitely exists.

=MATCH(AI17,S17:AG17,0) which gives #N/A

if evaluate the formulas compnonents (using F9) I get

=MATCH(0.03477,{0,0.0175,0.03477,0.07024,0.10014,0.11266},0)

which correctly returns 3

The cells I'm matching are formatted exactly the same way (0.00%).

Confused - Bug in 2007 as I've never had this before?

Thanks
TMJ

 

[Jonmo1]

I would suspect either your number in AI17, or numbers in S17:AG17 are not really numbers.

What do these return?
=ISNUMBER(AI17)
=ISNUMBER(S17) - Filled right to AG17

 

[Andrew Poulsom]

Maybe it's a floating point precision issue. Try:

=MATCH(ROUND(AI17,5),S17:AG17,0)

 

[Gerald Higgins]

I can't replicate this. When I input those values into S17:AG17, and used your formula, it returned 3.

Is it possible you have calculation set to manual ?

 

[TonyMJ]

Thanks Guys for the quick responses.

Andrew you are correct it is floating point precision and rounding works like a charm.

Thanks I can now stop banging my head on the desk and swearing at my laptop.