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

 

[njimack]

Assuming your table is in the range A2:B7 and the value you're looking for is in E2:
=INDEX(B2:B7,MATCH(E2,A2:A7,1))

 

[Jonmo1]

Try applying this to your lookup value
ROUND(LookupValue/1000,0)*1000

=VLOOKUP(ROUND(LookupValue/1000,0)*1000,A:B,2,FALSE)

 

[milwphil]

I tried: =INDEX(B2:B7,MATCH(E2,A2:A7,1))

It's rounding down perfectly but once I put 6501...it's still rounding down. I was hoping to get it to round up to the next threshold level.

Any suggestions?

Thanks,
Phil

 

[Jonmo1]

Try

=VLOOKUP(ROUND(E2/1000,0)*1000,A2:B7,2,FALSE)

ROUND(E2/1000,0)*1000 - this rounds E2 to the nearest 1000

 

[milwphil]

Jonmo1- Thanks for the quick response. I tried your solution. It works with smaller numbers;however the actual data I'm using:
Column A
5,000,000...........
10,000,000.........
15,000,000.........
20,000,000.........
25,000,000.........

I keep receiving #NA results for numbers inbetween (and preceeding 5,000,000 and numbers larger than 25,000,000).

It responds correctly with exact matches.

Your prior suggestions and any subsequent are greatly appreciated!!!!!

Thanks,
Phil

 

[Jonmo1]

then change both 1000 in my formula to 5000000
To round to the nearest 5Million.

 

[milwphil]

Amazing! I should have read your earlier posts closer! THANK YOU!

 

[morleyuk]

Just a quick addition if you're interested.

You don't need to do the divide and multiply.

Round will work with -1 etc to round to the nearest 10 etc

Round(Number,-5) will round to nearest 500K

 

[Jonmo1]

Just a quick addition if you're interested.

You don't need to do the divide and multiply.

Round will work with -1 etc to round to the nearest 10 etc

Round(Number,-5) will round to nearest 500K

No, that will round to the nearest 100K

-6 rounds to the nearest Million
-7 to the nearest 10Million
-8 to the nearest 100Million


You can't get to the nearest 5Million using just round by itself.