• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup

Rodrigo

New Member
Why does the vlookup function give the result of the value just above the one that I am looking for. For example, a series of values:

0.448;0.449;0.450;0.451, I'm looking for 0.451 and the answer comes up as 0.450.


Thanks in advance


Rodrigo
 

xld

Member
ARe you sure that the value is exactly 0.451,, either value, it may have other decimal places that are not showing.
 

Rodrigo

New Member
Yes, its exactly like this, even the number format is "general".

What I have is user defined occupancy rates that start at 0 all the way to 100, in increments of 0.01, the range is therefore rather large but frustrating because the lookup value is always out by a small fraction.


Regards
 

Hui

Excel Ninja
Staff member
Rodrigo


In your posts above you say that your looking for the value 0.451,

and then later you say that your list is 0 - 100 in 0.01 steps


Hence the value 0.451 doesn't exist in your list and the value just smaller than it is 0.45


I asume your list goes up by 0.001 not 0.01


If that is true I can't reproduce your problem


What version of Excel are you using ?
 

Rodrigo

New Member
Hui, yes sorry, it goes up in increments of 0.001, eg 0.4508; 0.4509; 0.4510; 0.4511; 0.4512 etc.

The version of excel is 2007.


Rodrigo
 

Rodrigo

New Member
Further to this, how can it be that if I get vlookup to look up another value and then afterwards change that value back to the one that I'm looking for, the answer comes up correctly but a further try gives the incorrect answer one again.


Rodrigo
 

Rodrigo

New Member
Sorted, instead of using excel's autofill for the data table, I converted the latter to formulas and it works.


Rodrigo
 
Top