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

[Guest]

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?

 

[Scoops]

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

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[Herbert Seidenberg]

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