How Do I Get VLOOKUP to Interpolate?

I have an array of values and am using the VLOOKUP function to pull the appropriate values from my table so I can run some calculations using those variables.

The problem I'm having is that the values being extracted by VLLOKUP are the *exact* values listed in the array, but I would prefer values that have been linearly interpolated. How can I do this?

I hope my description was clear.

Thanks!

-Christine

Thread770-51434

has several approaches. I found using INDEX and MATCH was the neatest

Cheers

Greg Locock

Thanks, Greg. That thread is just what I'm looking for!

I'm very surpised to learn that Excel doesn't have such a function. I was hoping that maybe there was some add-in or something that I missed that would this, but apparently there isn't.

Thanks again,

-Christine

Christine74

I'm not sure what you are working with, but with your sheet can you make several columns and do the vlookup as column 1, column 2 etc.???  This could return several answers for a single problem.  It then depends on how you write the formulas in the array or columns that you are looking up.  

I'm not that good at explaining so I hope this makes since and helps.

David  

I have found the worksheet function "forecast" to be very useful for interpolating.

Gavin.

There are a number of addin fuctions out there you can down load. Try looking up "Excel fuction interpolation" in Goole.

cheers
Kevin

As far as I know forecast cannot be used for interpolation unless the data has an exact linear relationship.

For example with data y=x^2, x= 1 to 10, using forecast at x=4 gives value of 22 instead of 16.

If I am doing anything wrong I would be pleased to know.

Regards
athomas236

Not sure why VLOOKUP is suggested for interpolation I always use the following function for interpolation.

regards,
athomas236

Function Interp(X, Y, Xe)
'   This function interpolates and extrapolates linearly on
'   arrays of X and Y values
'   Input is as follows
'           Array of X values that are in a sequence of increasing values
'           Array of Y values
'           Value Xe for which value of Y is required
    
'   Count number of X values
    For Each Item In X
    CountX = CountX + 1
    Next Item

'   Count number of Y values
    For Each Item In Y
    CountY = CountY + 1
    Next Item

    If CountX >= 2 Then GoTo 10
'   Error not enough values so end calculation
    Msg = "Need at least 2 values of X"
    Ans = MsgBox(Msg, vbOKOnly)
    Interp = "Error"
    GoTo 400
    
10  If CountX = CountY Then GoTo 100
'   Error should be equal number of X and Y values
    Msg = "Need equal number of X & Y values"
    Ans = MsgBox(Msg, vbOKOnly)
    Interp = "Error"
    GoTo 400
    
100 If Xe > X(1) Then GoTo 200
'   Xe less than smallest X
    Interp = Y(2) - ((Y(2) - Y(1)) / (X(2) - X(1)) * (X(2) - Xe))
    GoTo 400

200 If Xe < X(CountX) Then GoTo 300
'   Xe greater than largest X
    Interp = Y(CountX - 1) + ((Y(CountX) - Y(CountX - 1)) / (X(CountX) - X(CountX - 1)) * (Xe - X(CountX - 1)))
    GoTo 400

300 For Item = 1 To CountX
    If Xe >= X(Item) And Xe <= X(Item + 1) Then GoTo 310
    Next Item
310 Interp = (Y(Item + 1) - Y(Item)) / (X(Item + 1) - X(Item)) * (Xe - X(Item)) + Y(Item)

400 '
End Function