One way:

First - note that you're not really interpolating based on the range
of data - you're simply finding the linear interpolation between the
two closest values. The result you're seeking, 11, certainly
wouldn't fall on a smooth trendline given your data.


Public Function FindX(xRange As Range, yRange As Range, _
y As Range, Optional bAscend As Boolean = True) As Double
Dim maxX As Double
Dim maxY As Double
Dim minX As Double
Dim minY As Double
Dim matchPoint As Long
Dim matchType As Long

If bAscend Then
matchType = 1
Else
matchType = -1
End If
matchPoint = Application.Match(y, yRange, matchType)
If yRange(matchPoint) = y Then
FindX = xRange(matchPoint)
Else
maxX = xRange(matchPoint - bAscend).Value
minX = xRange(matchPoint - (Not bAscend)).Value
maxY = yRange(matchPoint - bAscend).Value
minY = yRange(matchPoint - (Not bAscend)).Value
FindX = (maxY - y) / (maxY - minY) * (maxX - minX) + minX
End If
End Function

Call as

= FindX(B37:B45,C37:C45,E27,FALSE)

Note that I made the default for y values in ascending order. The y
values must be sorted.

Note that you can accomplish the same thing with worksheet formulae:

=(INDEX(Ys,MATCH(Y,Ys,-1))-X)/(INDEX(Ys,MATCH(Y,Ys,-1))-(INDEX(Ys,MAT
CH(Y,Ys,-1)+1)))*(INDEX(Xs,MATCH(Y,Ys,-1))-INDEX(Xs,MATCH(Y,Ys,-1)+1)
)+INDEX(Xs,MATCH(Y,Ys,-1)+1)

where Xs, Ys, and Y are your x-range, y-range and unknown y.

In article ,
(Mark) wrote:

Need a function to return a X value corresponding to a Y using a list
of known X's and Y's.

Example: Find the X for Y=320000 interpolating between the known Y's
above and below the Y in question.
The list looks like this:
X Y
0 832900
5 498008
8 390988
10 340000
12 300000
15 255525
18 222355
20 204822
30 148614

The function should return X=11.

Known X's are in B37:45 and known Y's are in C:37:C45.
The Y in question is in E37.
I'd like the corresponding X to be in cell F37.
As I understand how VB functions work, the formula in F37 should be
something like =myfunction(B37:B45,C37:C45,E37).

Seems like a simple common problem and I'd appreciate suggestions
anyone may have.

Thanks, Mark