vlookup inconsistent

Question

Hi,

I am using a =vlookup on a calculated field. in some combinations of the input parameters the same result combines with diferrrent values froma a referenced table.

				%	Count
				0	Negative
				2	1
				3,2	2
				4	3
					4
v1	v2	v3	v4	sum	vlookup call	Comment
0,8	0,9	0,8	0,7	=SUM(A10:D10)	=VLOOKUP(E10;$E$2:$F$5;2;1)	returns 2: OK
0,9	0,9	1	0,4	=SUM(A11:D11)	=VLOOKUP(E11;$E$2:$F$5;2;1)	returns 1: should return: 2

Thanx,

Igor

Answer 1

You have managed to hit on two combinations that, although they appear identical, are actually slightly different in Excel's internal representation.  This is due to the fact that Excel, as most all spreadsheet computer based programs, adheres to an IEEE standard (754?) for double precision floating point numbers.  This is discussed in more detail in this  MSKB Article.

Using  an add-in that allows for increased precision, I see that the results of the two SUM formulas, to 30 decimal digits, are

 3.20000000000000017764

3.19999999999999973355

There are several ways around this when precision is important, as it is in your function.  The most reasonable is to ROUND the result to your desired level of precision.

=ROUND(SUM(A10:D10),2)

You can also set your worksheet to "Precision as Displayed", but this will also change the precision, irretrievably, for every cell on your workbook.

Answer 2

Hi,

This looks like a rounding error to me and is a function of the way Excel does arithmetic. In common with other applications Excel uses binary to do it's calculations and often there is no precise binary representation of a decimal number. Have a look here.

http://support.microsoft.com/kb/78113

Looking at you specific example you are using the 1 or TRUE switch as the last argument in vlookup so in the second example the formula is returning the largest match less than the lookup value (2)  hence it's returning 1.

I sugggest you modify your formula like this:-

=VLOOKUP(ROUND(E10,2),$E$2:$F$5,2,FALSE)

and this

=VLOOKUP(ROUND(E11,2),$E$2:$F$5,2,FALSE)

Both these now return 2. Note I've changed ; in the formula to , to match my locale so you will need to change them back.

Answer 3

That would be nice.  But inexact representations of certain values can be a problem in any number system.

Answer 4

Thank you for the suggested workaround. It actually works.

Though, but I find this an issue. Now I have to reinspect all the values calculated using this mehodlology.

The calculations should be executed as stated eliminating the need for workarounds.

Thanks again,

Igor