Formula: Vlookup based on a number that's not in the table, but needs to be looked up rounded (based on the table).

[milwphil]

Real quick:

I have a Vlookup problem and I'm not sure if there's a better solution.

I'm trying to come up with a formula that will be looked up on a table that may not specifically exist.

For example:
The user enters a value of 6303, I'd like the vlookup to return "Pushing the limit". If the user enters 6500 to return "Way too much"

The table:
Column
A B
1000 Too little
3000 Almost enought
4000 Enough
5000 Could be less
6000 Pushing the limit
7000 Way too much

Is vlookup the formula to be using?

How should I approach this problem?

Any help is greatly appreciated!

Thanks,
Phil

 

[morleyuk]

jonmo1 - yeah been a long day....

-5.5 will round to nearest 500k

 

[Jonmo1]

jonmo1 - yeah been a long day....

-5.5 will round to nearest 500k

You sure about that?


Yes, I know how long days can be...

 

[morleyuk]

Too long a day going back to sleep.....

 

[Jonmo1]

It would be cool if round could do that though....

 

[morleyuk]

Had to have another go....

Back to the multipy and divide and fractions but it seems to work...

=ROUND(A1*2,-13/2)/2

 

[Jonmo1]

That still has nothing to do with using x.5 in the round...

=ROUND(A1*2,-13/2)/2
same result as
=ROUND(A1*2,-6)/2
same result as
=ROUND(A1*2,-6.5)/2

 

[morleyuk]

Yeah the .5 doesn't work nor does the 13/2 just looks like it does...

The *2 and /2 do the magic.....

Couldn't see the wood for the trees....