Home 
Search 
Today's Posts 
#1




Interpolation with VLOOKUP
I'm trying to construct a workbook with the ability to interpolate between 2
values. If we consider two columns of data, for example: A B 100 97 110 120 135 135 157 166 What function or nested functions will return an "A" value if I input a "B" value of 147 ?  Dave_DD 
#2




Interpolation with VLOOKUP
Hi!
Depends on what result you're after: If you think the result shoud be 135: D1 = 147 =INDEX(A1:A4,MATCH(D1,B1:B4)) If you think the result should be 157: The table would need to be sorted in descending order =INDEX(A1:A4,MATCH(D1,B1:B4,1)) Biff "Dave_AD" wrote in message ... I'm trying to construct a workbook with the ability to interpolate between 2 values. If we consider two columns of data, for example: A B 100 97 110 120 135 135 157 166 What function or nested functions will return an "A" value if I input a "B" value of 147 ?  Dave_DD 
#3




Interpolation with VLOOKUP
Easiest with a few helper cells. If you don't like that, you can always
compact them to one formula, but I think this way it is more clear what happens. I assume your table (without headers) in A1:B4 and the item to look for in C1 In D1: =MATCH(C1,A1:A4) In E1: =INDEX(A1:A4,D1) In E2: =INDEX(A1:A4,D1+1) In F1: =INDEX(B1:B4,D1) In F2: =INDEX(B1:B4,D1+1) Your formula: =E1+(C1F1)/(F2F1)*(E2E1) Gives 143.5161 Is that what you were looking for?  Kind regards, Niek Otten "Dave_AD" wrote in message ... I'm trying to construct a workbook with the ability to interpolate between 2 values. If we consider two columns of data, for example: A B 100 97 110 120 135 135 157 166 What function or nested functions will return an "A" value if I input a "B" value of 147 ?  Dave_DD 
#4




Interpolation with VLOOKUP
Thanks for the help. It works like a charm.
 Dave_DD "Niek Otten" wrote: Easiest with a few helper cells. If you don't like that, you can always compact them to one formula, but I think this way it is more clear what happens. I assume your table (without headers) in A1:B4 and the item to look for in C1 In D1: =MATCH(C1,A1:A4) In E1: =INDEX(A1:A4,D1) In E2: =INDEX(A1:A4,D1+1) In F1: =INDEX(B1:B4,D1) In F2: =INDEX(B1:B4,D1+1) Your formula: =E1+(C1F1)/(F2F1)*(E2E1) Gives 143.5161 Is that what you were looking for?  Kind regards, Niek Otten "Dave_AD" wrote in message ... I'm trying to construct a workbook with the ability to interpolate between 2 values. If we consider two columns of data, for example: A B 100 97 110 120 135 135 157 166 What function or nested functions will return an "A" value if I input a "B" value of 147 ?  Dave_DD 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
VLOOKUP Limitations  Excel Worksheet Functions  
Have Vlookup return a Value of 0 instead of #N/A  Excel Worksheet Functions  
help with interpolation and limit of interpolation  Excel Discussion (Misc queries)  
vlookup data hidden within worksheet  Excel Worksheet Functions  
Vlookup info being used without vlookup table attached?  Excel Worksheet Functions 