Sep 13, 2013

Excel Gotcha–Fractional Numbers

TODAY I learned (the hard way) about a subtle bug in Microsoft Excel. It seems that to the VLOOKUP function looking for a matching value in a range, two equal fractional numbers are not always equal.
Fractional numbers, technically known as floating-point numbers because of the way computers store them internally, sometimes give Excel a headache. If you want to see this for yourself, try out the following exercise:
image
Edit: the formula above should be '=VLOOKUP(3.3, tblLookup, 2, FALSE)'.

You will find yourself with the following error:
image
Even more nefariously, if you use the range lookup option:
=VLOOKUP(3.3, tblLookup, 2, TRUE)
Excel will give you an actively incorrect result:
image
So, be extremely wary of using fractional numbers as lookup keys in VLOOKUP functions. If you must, then use the techniques described in Microsoft’s Knowledge Base article on this issue.

No comments: