Linear Interpolation function for Excel VBA

interpolate.bas

Function interp(X As Double, xRange As Range, yRange As Range) As Double

    ascending = xRange.Cells(1) < xRange.Cells(2)
    With WorksheetFunction
        If ascending Then i = .Match(X, xRange) Else i = .Match(X, xRange, -1)
        Set x1x2 = Range(xRange.Cells(i), xRange.Cells(i + 1))
        Set y1y2 = Range(yRange.Cells(i), yRange.Cells(i + 1))
        interp = X * .Slope(y1y2, x1x2) + .Intercept(y1y2, x1x2)
    End With
    
End Function

There are many like it but this one is mine. All the work is handled by built-in Excel functions (Match, Slope, Intercept). It supports inputs as rows or columns, ascending or descending.

A simplified version of this function (supporting only ascending columns), can be entered in the Name Manager in new versions of 365. Create a new name called interp, with this as the formula:

=LAMBDA(xval,x,y,LET(i,MATCH(xval,x),mb,LINEST(OFFSET(y,i-1,0,2),OFFSET(x,i-1,0,2)),INDEX(mb,1)*xval+INDEX(mb,2)))