use vlookup or other to find the nearest values (<) or interpola
 

How can I get VLOOKUP to find ither the nearest value to the one being
searched for, or to interpolate between the two nearest values?

use vlookup or other to find the nearest values (<) or interpola
 

Hi Rodney

Use the Range_lookup value TRUE to return the highest figure lower than
your Lookup_value i.e.

if A1 = 1, A2 = 2, A3 = 4 then

=VLOOKUP(3,A1:A3,1,TRUE) will return "2" where 3 is not found and 2 is
the highest number not exceeding 3

Regards

Steve

use vlookup or other to find the nearest values (<) or interp
 

But what if the we wanted to lookup 3.5... is there a way to get it to return
'4' since it is closer numerically?

Rodney



"Scoops" wrote:

Hi Rodney

Use the Range_lookup value TRUE to return the highest figure lower than
your Lookup_value i.e.

if A1 = 1, A2 = 2, A3 = 4 then

=VLOOKUP(3,A1:A3,1,TRUE) will return "2" where 3 is not found and 2 is
the highest number not exceeding 3

Regards

Steve

use vlookup or other to find the nearest values (<) or interp
 

Are the lookup values integers, then you can use

=LOOKUP(ROUND(3.5,0),A2:A20,B2:B20)

with a sorted list in A2:A20 will lookup 4 for 3.5 and 3 for 3.4


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Rodney" wrote in message
...
But what if the we wanted to lookup 3.5... is there a way to get it to
return
'4' since it is closer numerically?

Rodney



"Scoops" wrote:

Hi Rodney

Use the Range_lookup value TRUE to return the highest figure lower than
your Lookup_value i.e.

if A1 = 1, A2 = 2, A3 = 4 then

=VLOOKUP(3,A1:A3,1,TRUE) will return "2" where 3 is not found and 2 is
the highest number not exceeding 3

Regards

Steve

use vlookup or other to find the nearest values (<) or interp
 

If none of your numbers are integers
and the list is not sorted, see
http://tinyurl.com/nq7xk
or search this site: Closest Match, March 15