Is there a function is excel where the nearest value can be looked up?
For an example consider the following.
> A1
1>1000
2>1500
3>2000
Assume this information is in the range (A1:A3). if I use vlookup(1499,A1:A3,1) this returns 1000. Is there another function that would choose 1500 which is the closest match.
I would appreciate any comments on this.
Thanks,
Rennie:puzzled:
Hi Rennie
MROUND should do the trick:
=VLOOKUP(MROUND(1499,500),A1:A3,1,FALSE)
FALSE finds an exact match.
Cheers,
Richard Fuller
or. more generally (in case the 500 increments were only for example):
=INDEX(A1:A3,MATCH(MIN(IF(A1:A3-B1>=0,A1:A3,FALSE)),IF(A1:A3-B1>=0,A1:A3,FALSE),0))
array entered, using control + shift + enter, not just enter. (where b1 contains the value to lookup).
Thank you for your reply Richard. I posted a bad example.
Paddyyd - thank you. You are closer to solving my problem.
Assume Now.
>A1
1>1200
2>2000
3>3000
B1=Cell to Lookup = 1250
Your formula returns 2000, is it possible to make it return 1200 because this is the nearest match.
Sorry for the trouble.
I appreciate your help Richard ans Paddy.
Thanks,
Rennie
what happens when a lookup value is equidistant between 2 other values?
Thank you for your response Paddy.
It can either be the lower or the higher number. I am not too concerned about this.
Thanks,
Rennie
see the 'closest match' formulas here:
http://www.cpearson.com/excel/lookups.htm#ClosestMatch
they return closest above & closest below. How you choose between them is up to you!
Thank you very much paddy exactly what I wanted.
Rennie
Don’t have an account yet? Register yourself now and be a part of our community!
