 # Choosing the nearest value [SOLVED]

• Is there a function is excel where the nearest value can be looked up?

For an example consider the following.

&gt; A1
1&gt;1000
2&gt;1500
3&gt;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).

Paddyyd - thank you. You are closer to solving my problem.

Assume Now.

&gt;A1
1&gt;1200
2&gt;2000
3&gt;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.

Thanks,
Rennie

• what happens when a lookup value is equidistant between 2 other values?

It can either be the lower or the higher number. I am not too concerned about this.

Thanks,
Rennie

• Thank you very much paddy exactly what I wanted.

Rennie

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!