G Guest Apr 5, 2006 #1 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?
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?
S Scoops Apr 5, 2006 #2 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
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
G Guest Apr 5, 2006 #3 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
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
P Peo Sjoblom Apr 5, 2006 #4 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
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
H Herbert Seidenberg Apr 5, 2006 #5 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
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
