Hi,

I have an interpolation function that I have tried to write in VBA. The results of the function are correct when the given X value (in my case sigma) is equal to or outside the bounds of the highest or lowest sigma. But when the given X value or sigma for which I'm trying to interpolate is between the highest and lowest sigma I get incorrect results. I'm not sure where the code is wrong or why its giving incorrect results for this subset of scenario. I'd appreciate any insight. I have attached a sheet with sample data that also contains the VBA code under module1. I will also include the code below.

Thanks

examplesheet.xlsm

Function interpsigma(lowsigma As Double, lowsigma2 As Double, highsigma As Double, highsigma2 As Double, lowvol As Double, lowvol2 As Double, highvol As Double, highvol2 As Double, sigmas As Range, vols As Range, targetsigma As Double)

If targetsigma = lowsigma Then
    interpsigma = lowvol
    
ElseIf targetsigma = highsigma Then
    interpsigma = highvol
    
ElseIf targetsigma < highsigma And targetsigma > lowsigma Then
    Dim firstindexpos As Double
    Dim secondindexpos As Double
    Dim firstsigmavalue As Double
    Dim secondsigmavalue As Double
    Dim firstsigmavol As Double
    Dim secondsigmavol As Double
            
    
    firstindexpos = Application.WorksheetFunction.Match(targetsigma, sigmas, 1)
    firstsigmavalue = Application.WorksheetFunction.Lookup(targetsigma, sigmas)
        
    If firstsigmavalue = targetsigma Then
    secondindexpos = firstindexpos
    Else
    secondindexpos = firstindexpos + 1
    End If
    
    secondsigmavalue = Application.WorksheetFunction.Index(sigmas, secondindexpos)
    
    firstsigmavol = Application.WorksheetFunction.Lookup(targetsigma, sigmas, vols)
    secondsigmavol = Application.WorksheetFunction.Index(vols, secondindexpos)
    
    If firstindexpos = secondindexpos Then
    interpsigma = firstsigmavol
    Else
    interpsigma = ((targetsigma - firstsigmavalue) / (((secondsigmavalue - firstsigmavalue) * (secondsigmavol - firstsigmavol)) + firstsigmavol))
    End If
    
    
ElseIf targetsigma > highsigma Then
    interpsigma = (((highvol - highvol2) / (highsigma - highsigma2)) * (targetsigma - highsigma)) + highvol
    
ElseIf targetsigma < lowsigma Then
    interpsigma = (((lowvol - lowvol2) / (lowsigma - lowsigma2)) * (targetsigma - lowsigma)) + lowvol

End If


End Function