Excel MATCH function seems to be broken. Issue description and investigation details. Any explanation or solution to this?

Question

I'm a fairly intensive and experienced Excel user, used to work with INDEX and MATCH functions and with strings, so I am also used to the typical issues with errors with the MATCH function due to different cell formatting, unnoticed spaces and such. I am also familiar with multiple solutions to this kind of problems, such as converting text into numbers and the other way around by using TEXT() or VALUE() functions, or simpler approaches such as adding --, +0, *1, "" & or other similar operations to convert between formats. What I found this time was a completely different kind of error that is value dependent without changing the format.

In my spreadsheet I was just trying to find what row of a list of values in 'cm' corresponded to a value obtained elsewhere that was in 'mm'. Therefore, I supposedly just had to match the input value divided by 10. This worked initially, until I realized that it did not work for certain values. In the first image below, it can seen that match is not able to find the value '55.025' but has no problem finding '64.025', even though the subsequent cells on the list just contain the value from B4 minus the value next to them on column A.

After not being able to trace the error, I created a spreadsheet to test multiple different combinations and try to identify a pattern for the behavior of the MATCH function. But if anything it just confused me further, showing that not only it behaves differently with different values, but that the different ways of transforming text into numbers do not behave consistently either. I have actually identified 6 different possible scenarios based on what values are actually entered.

It is difficult to summarize the results, so you should just examine the spreadsheet and make your own tests in it. I will just explain its structure shown in the picture below.

The reference value is that of cell D3 (grey), I just added the decimals there to simplify the input of different values entered in B3 (blue). The cells in orange and yellow on column D are different ways to format the value of the D3 (grey). That is:

D7   ="" & D3/10
D11  =--D7
D15  =D3/10

Corresponding to text format, text converted to number and a plain number, respectively.

The green cells at the bottom of column D are just the list of values within which the MATCH function searches, obtained by subtracting the values from column C to the value in D18.

Further right (columns G:Q), row 3 contains the row MATCH should find the searched value within the green colored array of column D. Row 5 lists the type of modification implemented for the values of column D. Above them (rows 6, 10, 14) there is a check of whether these values are equal to the corresponding cell within the green array of column D. And below (rows 8, 12, 16) are the actual MATCH formulas for this values.

Below all this there is a list of the different values that show each kind of behavior, ranging from being found in the array regardless of the modifications (as long as they are numbers), to not being found at all, with several different cases in-between.

The "Testing_B" tab is exactly the same as the "Testing" tab but the value for cell D18, instead of being pasted from my initial spreadsheet is entered manually. That results in it are a different set of weird behaviors, even though the originally pasted value is supposed to be identical to that of the former tab.

You can download the spreadsheet below and verify the behavior of the different listed values when entered on cell B3 (blue). The valid range is currently 44 to 64.

Spreadsheet MATCH_ERRORS.xlsx

I hope that someone can figure out some kind of explanation from all this.

---

*Edit (2020-05-07):

Possible Rounding Issues

Regarding the rounding issues as possible underlying cause pointed out by Justin Doward, I noticed that Excel seems to use a different level of precision when checking whether two cells are equal than when checking if two cells match (MATCH function). I just made a simple test, and that difference is of one or two orders of magnitude depending on the compared values (picture below). It still does not explain why Excel is somehow changing the actual value of a cell with some approximation that then does not match the supposed value of that cell. The values used in the original problem are not even close to a level of precision that would be expected to face potential rounding errors. This would just seem to indicate perhaps an even more serious underlying problem.

Answer 1

Both Sheets and Excel make certain assumptions due to the rounding issues inherent in the IEEE standard which both are based on.

So sometimes Sheets will appear to work where Excel does not, but there are instances where Sheets will return the "wrong" answer also.

For example:

Excel  A1:  =3*(4/3-1)-1
       B1:  =A1=0  -->TRUE

Sheets:  A1:  =3*(4/3-1)-1
         B1:  =A1=0 --> FALSE

In your particular example, if, instead of entering B4: 6.4025 you enter the formula =B2/10+A8, your MATCH function will work.

These limitations apply, in one way or another, to ALL spreadsheet programs that use the IEEE standard for storing double precision numbers.

Answer 2

It appears to be a rounding error, if you round the number in both formula (B4:B8 and E1 on my sheet) first the match works fine.

=MATCH(ROUND(B2/10,4),$B$4:$B$8,0)

=ROUND($B$4-A8,4)

Answer 3

So far, the only effective solution I have found for this issue is to transform into text all the values involving decimal numbers that are included in the MATCH function. For instance, the formula for cell G8 in the image below would become:

G8:   =MATCH("" & G7,"" & $D$18:$D$38,0)

And if the test to verify whether the cells are equal is to be changed accordingly as well, it would become:

G6:   ="" & G7="" &@INDEX($D$18:$D$38,G$3)

But this is simply a workaround to avoid the problem that is still there. The MATCH function should do something like that automatically.