I didn't go through all the hits in the google archive (the link Tom
provided) but using a formula for a general purpose solution is kinda
messy. By contrast a user defined function (UDF) might be a lot
cleaner. And, it can written to handle multiple a vector of input
values and return a vector of results with a single call!

In any case, if you want to stick with formulas, you would need to do
the following. Suppose the x and y data are in columns A and B
starting with row 1 (i.e., no header).

Then, define the following names (Insert | Names Define...):
XVals =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
YVals =OFFSET(XVals,,1)
XMax =INDEX(XVals,COUNTA(Sheet1!$A:$A))
XMin =INDEX(XVals,1)
YMax =OFFSET(XMax,,1)
YMin =OFFSET(XMin,,1)

Now, if you want the interpolated y value corresponding to the x value
in D2, use

=IF(OR(D2<XMin,D2XMax),"out of bounds",IF(ABS(D2-XMax)
<0.00000001,YMax,FORECAST(D2,OFFSET(YMin,MATCH(D2, XVals,1)-
1,0,2,1),OFFSET(XMin,MATCH(D2,XVals,1)-1,0,2,1))))

I wrote a UDF for linear interpolation some time back. I will try and
find it, but no promises.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hello,

I am trying to create a formula that will lookup values in a column and find
those values in another column and then place the value in the row next to it
in a specific cell. So far this works with the formula
(=VLOOKUP(I6,$D$11:$E$223,2)). Now most of the time the value for cell I6
(thru I n) will not be found in the list ($D$11:$E$223) so I want to revise
the formula to do one of two things:

1. Always "interpolate" between points even when an exact match is found in
($D$11:$E$223)

2. When the value for cell I6 (thru I n) is not found then find the values
less than and greater than I6 and interpolate between the two points a value
based on the slope of the function defined by the two sets of corresponding
data.


this is driving me insane!!!! Please help me! I have 20 years of data to
enter and if I do it manually it will take 20 years.