INDEX/MATCH error cannot be explained

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

Originally Posted by lsargent

For the above range of %'s, all return correctly except 3.99%.
[....]
=INDEX($B$1:$B$20,MATCH((C10/100),$A$1:$A$20,0))

Change the formula to =INDEX($B$1:$B$20,MATCH(ROUND(C1/100,5),$A$1:$A$20,0)).

Round to 5 decimal places because you want comparisons to be accurate to 3 percentage decimal places.

Do not round to an arbitrary number of decimal places like 10, as some people suggest. There is always example that will fail.

The problem is an anomaly of 64-bit binary float-point, which is how Excel represents values internally. Consequently, most decimal fractions cannot be represented exactly.

Note that 3.99% is 0.03990. The internal representation is exactly 0.0398999999999999,97968291864935963531024754047393798828125.

In contrast, 3.99/100 is represented exactly as 0.0399000000000000,0490718576884319190867245197296142578125.

I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel formats.

MATCH(...,0) does an exact binary comparison. Obviously, the two binary representations are not the same.

It was only a coincidence that your original formula worked for other values in columns A and C.

It might be tempting to suggest changing the formula to =INDEX($B$1:$B$20,MATCH(C1/100,$A$1:$A$20,1)).

But that works, too, only by coincidence. The binary representation of 3.99/100 is larger than the binary representation of 3.99%. But that is not always the case.

Originally Posted by lsargent

I can confirm and validate a boolean TRUE/FALSE statement that (C10/100) = A10

That works because sometimes, Excel "=" does not do an exact binary comparison. Instead, Excel compares the values rounded to 15 significant digits.

For this particular example, both 3.99% and 3.99/100 round to 0.0399000000000000.

This is part of a dubious heuristic that is described under the misleading title "Example When a Value Reaches Zero"
in http://support.microsoft.com/kb/78113.

The heuristic is not applied consistently. For example, C10/100 - A10 = 0 would return FALSE (!).