VLOOKUP...bug?

[JonnyB]

I know its not possible that I've found a bug and I'm doing something wrong, but can someone please explain this odd behavior to me...

I am using vlookup in the following way:

=VLOOKUP(0.5+0.1,V17:W17,2,FALSE)

with the 0.6 in the first column, this works fine...however, if i put 0.8 in the first column and use:

=VLOOKUP(0.7+0.1,V17:W17,2,FALSE)

I get a value not available error. So it works for most numbers, but just not some. I've tested it on the same one row table to ensure thats its not a formatting issue. Anyone know why it would work inconsistently in this situation? I'm using 2007. Thanks in advance.

[teylyn]

Hard to say without seeing the true values in your lookup table. Can you post a workbook?

[contaminated]

Hi
Bu it works for me. See sample. Please upload your own....

[JonnyB]

Thank you both for taking time to have a look. I've attached the sheet, its occuring throughout certain cells in the "frequency" column depending on the value, but see cell V19 for a simple example.

[teylyn]

Hah, Vusal! you've calculated the 0.8 value. Try to hard code 0.8 into cell A8 and see.

JohnnyB, I confirm that this is odd. I could re-create it in both 2003 and 2010. I suspect it has to do with the floating point bug that has haunted Excel for quite a while.

see http://www.microsoft.com/communities...&cr=&sloc=&p=1

[JonnyB]

Ahh...so I'm not crazy...thanks for that info. I guess I'll just use calculated values as well. Thanks again guys.

[6StringJazzer]

I saw very similar behavior in another thread on this board that I considered likely to be a bug. The VLOOKUP was seeking a value entered as a constant in a cell, and was failing to find the same value in a table. The value in the table was calculated and was an exact value that matched the constant value, yet VLOOKUP failed to match it. So a bug is not unthinkable.

I was unaware of a known floating point bug, thanks for the link.

[JonnyB]

While this post is open, I might as well ask a related question that I can't seem to figure out. Is there a way with VLOOKUP or MATCH (or any other function(s)) to return the smallest value that is larger than the lookup value when using an ascending list?

For example, if my lookup array contains (the two lines skipped between 3 & 7 represent cells containing "" )...

1
3


7
8
10

13

and I use 5 as my lookup value, I'd like to get 7 as the result, not 3 (or blank). Any thoughts?

[shg]

=INDEX(A1:A9, MATCH(TRUE, A1:A9>5, 0)), array-entered.

[JonnyB]

Perfect...thank you shg.