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:
Edit: the formula above should be '=VLOOKUP(3.3, tblLookup, 2, FALSE)'.

You will find yourself with the following error:
Even more nefariously, if you use the range lookup option:
=VLOOKUP(3.3, tblLookup, 2, TRUE)
Excel will give you an actively incorrect result:
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: