Forum Discussion
MATCH function not finding exact match
I initially began this conversation with a problem, but, as I was writing, I was able to fix my issue. So, seeing I had already written most of it, I though I would include my findings in case ...
Probably a rounding error related to floating point math.
https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/
If you use the Round function for your calculations in column I (round to a level of precision appropriate for your needs), then I believe you will get the answer you expect. I rounded to 20 decimal places and it matched to 1.18.
https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/
If you use the Round function for your calculations in column I (round to a level of precision appropriate for your needs), then I believe you will get the answer you expect. I rounded to 20 decimal places and it matched to 1.18.
Interesting case. It looks like MATCH internally also takes into account 17 digits as some other functions (e.g. RANK). ROUND returns 15.