VLOOKUP calculation column indexing error in Excel 2008

[bearmark]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

There is a significant calculation error issue with Excel 2008. When using VLOOKUP and specifying a column index in the col_index_num parameter, then the column is always indexed 1 less than it should be (e.g. if 14 is specified, the index used is 13). I've verified the correct indexing in both Excel 2003 SP3 for Windows and Excel 2004 for Mac. The same spreadsheet will yield different results in these versions as compared to Excel 2008.

I believe that this is a serious error! Has anyone else confirmed this?

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

There is a significant calculation error issue with Excel 2008. When using
VLOOKUP and specifying a column index in the col_index_num parameter, then
the column is always indexed 1 less than it should be (e.g. if 14 is
specified, the index used is 13). I've verified the correct indexing in both
Excel 2003 SP3 for Windows and Excel 2004 for Mac. The same spreadsheet will
yield different results in these versions as compared to Excel 2008.

I believe that this is a serious error! Has anyone else confirmed this?

I can't reproduce this.

Does it happen in all workbooks, or just one?

Are any of your parameters calculated?

 

[bearmark]

Actually, the col_index_num is calculated (i.e. "C3/5%+2", where C3 is a constant value of 60%). The calculated value is 14, but the index being used is 13. I've tried some more variations based on your question. If I change the formula to "ROUND(C3/5%+2)", then it works. Similarly, if I enter a constant or remove the percentages, then it works. The problem seems to be related to the percentages, but I've confirmed that the figures were "0.6" and "0.05" and it fails.

 

[JE McGimpsey]

Actually, the col_index_num is calculated (i.e. "C3/5%+2", where C3 is a
constant value of 60%). The calculated value is 14, but the index being used
is 13. I've tried some more variations based on your question. If I change
the formula to "ROUND(C3/5%+2)", then it works. Similarly, if I enter a
constant or remove the percentages, then it works. The problem seems to be
related to the percentages, but I've confirmed that the figures were "0.6"
and "0.05" and it fails.

The VLOOKUP function takes the truncated integer value of the
calculation for its index, so if C3 is the result of a calculation and
displays "0.6", but is actually represented internally
"0.5999999999999", then you'll get 13 instead of 14.

This is a phenomenon that everyone who uses a spreadshseet application
needs to understand.

All finite precision math is subject to rounding errors. For instance,
the value "one-third" can be represented exactly as "1/3", but cannot be
represented exactly in a finite number of digits, e.g., "0.3", "0.33",
"0.3333333333333333333", etc. The same is true in the binary
representation in computer math.

So any spreadsheet application that uses or creates fractional values
needs to have functions analyzed for vulnerability to rounding errors.

 

[bearmark]

So, I understand that and that's why I explicitly said that C3 was an input constant value of 60% (i.e. no calculation). Changing the formula to 60%/5%+2 yields the same results even though .6/.05+2 = 14 (and not 13.99). Note that if I use 60/5+2, then the results are correct (i.e. it is definitely calculating to 14 in this case). Also, when this formula is used in a cell it calculates correctly, but when you use it for col_num_index in the VLOOKUP function, it fails.

I'll remind you that this same cell calculates correctly on Excel 2003 for Windows and Excel 2004 for Mac!!!!!!!!!!!

 

[JE McGimpsey]

So, I understand that and that's why I explicitly said that C3 was an input
constant value of 60% (i.e. no calculation). Changing the formula to 60%/5%+2
yields the same results even though .6/.05+2 = 14 (and not 13.99). Note that
if I use 60/5+2, then the results are correct (i.e. it is definitely
calculating to 14 in this case). Also, when this formula is used in a cell it
calculates correctly, but when you use it for col_num_index in the VLOOKUP
function, it fails.

I'll remind you that this same cell calculates correctly on Excel 2003 for
Windows and Excel 2004 for Mac!!!!!!!!!!!

Well, on my MBP, I get the same result as XL04 and XL03, and don't see
the rounding error that you're getting (I get 14 exactly).

So there must be something else going on - is it possible that the error
is in the match (i.e., VLOOKUP's actually matching a different row)
instead of the index?

In general, though, unless you're using integer math (e.g., 60/5), you
*cannot* rely on integer results. 0.6 is not a finite binary number
(it's double precision floating point representation is

00111111111010011001100110011001100110011001100110011001100110011

with the last 4 digits repeating). Likewise, 0.05 is represented as

00111111101011001100110011001100110011001100110011001100110011010

where you can see that the last two digits have been rounded up. When a
division operation is carried out, XL keeps some extra digits to help
with rounding, but the rounded end result still may not be an integer.

 

[bearmark]

So that's a nice explanation of why the error might exist, but it's a useless explanation from a practical perspective. Excel supports some level of precision that is not being properly rounded to in this function for the column index. In other words, Excel 2008 is WRONG! The proof exists in that the previous versions produce expected and correct results and 2008 does not. Regardless of how the implementers chose to write the code, when an obvious calculation produces wrong results, then it's wrong. It's as simple as that.

When I round the *finite* result, the correct answer is given. When I don't, the incorrect answer is given. When I calculate the same result in a cell by itself, then I get the correct and FINITE result. When I use it in the col_num_index field of VLOOKUP, then I don't.

I don't see how I can get any simpler that this. I'll go ahead and post this on MacFixit so that all Mac users can know about this error and don't get bitten by they as I did.

 

[JE McGimpsey]

In other words, Excel 2008 is WRONG!

I still can't reproduce that on any of my machines, so while I'm not
denying that you're seeing that, I also can't, at this point, attribute
the problem to XL rather than something else.

Given that the calculation engine was redesigned for both XL07 and XL08
it's certainly possible, but if so, it's evidently related to something
that's going on in your system and not mine (or some of my clients that
I tried yesterday).

 

[bearmark]

I've got a test file prepared that clearly demonstrates the error. Do you have an address that I can send it to?

 

[JE McGimpsey]

I've got a test file prepared that clearly demonstrates the error. Do you
have an address that I can send it to?

you can send it to the address I post at.

 

[Todd Aton]

Please send me a copy of the file that demonstrates the error.

Todd Aton
Macintosh Business Unit
Microsoft Corporation
(e-mail address removed)

Microsoft makes no warranties, express, implied or statutory, as to the
information in this post.