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

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.

Related