Lookup table...nearest value (1 Viewer)

[captnk]

This is a repost from queries which failed to get any response over the last 10 days.

I need a method similar to VLOOKUP in XL,
that can find the nearest value to the specification from a lookup table.

The basic problem is that the data is in round figures,but after I apply a percentage adjustment to them they no longer match values in the lookup table exactly,and therefore find nothing.

It is not possible to have every value listed in the look up table as that would necessitate a huge table with say every value between 1 and 100,,listed to 2 decimal places.....so its just not on.

Any ideas welcome

 

[Rich]

What do you mean by nearest value, what value would you want to return for say 2.5?

 

[captnk]

Lookup table Nearest value

Tks Rich

Ok heres an idea of what I am trying to do
The look up table contains a series of adjustment values,related to the base value
So i could have the follwing situation

Value lookupvalue
2 4.3
2.5 5.6
3 7.0


Those values match.
However if ithe pc generates a value of after a recalc of (say)
Value lookupvalue
2.75 (no equivalent)
but i need it to return 7 (being the next (higher (max)) adjustment value available in the lookup table)
Hope that clarifies it a bit.

Actually I did find a posting on here with a zip file attached "usinglookuptable.zip),which seems to have the general idea,but will take me a while to (hopefully ) get it to work for my purpose.
The principle in that was the following SQL
"SELECT StudentID, Score, (Select Min(Grade) from LookupTable where Marks < Score) AS Grade
FROM StudentScores;"
Its early days with that,but just maybe it has the idea.
Any other ideas most welcome.
Thanks
Captnk

 

[captnk]

Lookup Table Nearest value

Tks Rich et al.
I appear to have resolved my own problem,finally with a bit of reconstruction from this handy little Zip file I found in a much earlier post.

So I will repost for others...Sorry I would acknowledge the original author,but I am unable to recall.