1

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.

Error sample image

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.

Error investigation spreadsheet image

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.

Rounding error test. Equal vs MATCH

2
  • Interestingly, if you upload your file to Google Sheets and convert it to Google Sheets format, all your matches seem to work pretty well! Looks like this could be a bug in Microsoft Excel. Check this out, I simply converted your file to Google Sheets without making any changes at all. May 7, 2020 at 0:00
  • Interesting. Yes, it definitely looks like an Excel bug. I tried this with Office 365 versions 1908 and 2004 with the same result. Perhaps with the conversion to Google Sheets it converts these weird undetermined formats into something uniform and it works consistently. The original spreadsheet where the problem first showed up is a fairly big one with ActiveX Controls and macros. I am not sure Google Sheets would handle that properly... I actually tried and it doesn't even show properly. I also tried to save from Google back to Excel and the problems show up again when opening with Excel.
    – ZZR
    May 7, 2020 at 3:12

3 Answers 3

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.

1
  • Yes, though 4/3 obviously does need to be rounded. =4*(5/4-1)-1 does not have such issues. I was not aware, however, that =40*(41/40-1)-1 does also share the same kind of problems, but at least in Excel the result is not 0 but -3.55271E-15. In that case, I think that the MATCH() function is not implemented properly. It should be restricted to integers and text, or incorporate some way to avoid this ambiguity when comparing decimal values, which are considered to be equal by Excel for all other purposes except for the MATCH function. Any assumptions made need to be consistent.
    – ZZR
    May 7, 2020 at 23:28
0

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)

enter image description here

1
  • I just added a small section about the rounding issues. This may provide a workaround to the problem but at the same time would seem to indicate a possible more serious underlying issue about how Excel handles the values of the cells.
    – ZZR
    May 7, 2020 at 16:16
0

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.

Image of proposed workaround]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.