MATCH function not finding exact match

[knov_ga]

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 anyone else has a similar issue. Note, I fixed my issue, but I'm not certain I understand what was causing the problem. Perhaps someone else can elaborate on that.

My issue...

The MATCH function is not returning the correct value even though the exact match is clearly in the list. Instead, it returns the smallest value that is greater than the lookup value (option -1). Of those that I've tried, It only seems to occur with one specific lookup value (50).

I thought it may be an issue with my main spreadsheet as it's pretty involved and relatively large. So, I copied the raw date for one column while maintaining the find value, search array, and return array. Same result.

The included image illustrates the issue. The spreadsheet is also attached.

(1) With a lookup value of 50, the expected result in cell I1 is "59/1.18".

Item (1) - Lookup Value

(2) However, the result is "59/1.15".

Item (2) - Incorrect Return Value

(3) Evaluating the formula, you can see that the MATCH function is return an offset of '7'.

Item (3) - Wrong Offset

(4) The incorrect offset is then passed to INDEX where the wrong value "1.15" is returned.

Item (4) - Wrong Return Value

(5) It's clear to see that an exact match of 50 does exist.

Item (5) - Match clearly present

The same issue exists in cell I2. Here it's calculating ratio from return value to lookup value. Again, it's returning one offset less than it should, resulting in an incorrect calculation.

I will add, it could be a data type issue or something. If I remove the formula from cell I15 and replace it with the value 50, the MATCH function returns the correct result.

Additionally, if I round the calculated result in cell I15, the MATCH function works.

My resolution...

For my application, many of my calculations were included to simply eliminate manual entry of static values. You'll note column 'G' has calculated data from row '8' to row '200'. If I remove all of the calculations and simply replace all the data in column 'G' with static values, it all works. 

So, somewhere deep in Excel, there must have been some calculation that didn't end in an exact result. This error then propagated up to the MATCH function.

[#2]

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...

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.

[#3]

@JMB17 

Interesting case. It looks like MATCH internally also takes into account 17 digits as some other functions (e.g. RANK). ROUND returns 15.