Excel Reverse (bi)linear interpolation functions

Reverse interpolation within a 2-dimensional table is much easier done by using a user defined VBA function or LAMBDA equivalent (defined directly within Excel's Name Manager) than by using regular Excel formulas. LAMBDA functionality exists since Office365/Excel 2021, and executes faster than VBA.

Reverse bilinear interpolation is the reverse of Bilinear interpolation (separate page). Two options:
- Given Z-value (a value within a 2-dimensional data table), given Y-value (axis), it brings the X-value (axis).
- Given Z-value (a value within a 2-dimensional data table), given X-value (axis), it brings the Y-value (axis).
- Of course, it only works if given Z-value can indeed be "found" at a certain X and Y within the table.
Demo file contains reverse bilineair interpolation functions reverseinterpolateZY and reverseinterpolateZX (VBA) and their LAMBDA equivalentsL_reverseinterpolateZY and L_reverseinterpolateZX.
Also reverse linear interpolation within 1-dimensional table is possible with the helper functions, as can be seen at the bottom of the demo file.

Download demo file (47kB - downloaded 612 times)

reverseinterpolateZXY

Calculation explanation of above example - Reverse bilinear interpolation

STEP 1. Search Y = 75 lies between Y = 60 and Y = 80. Y-fraction = (75 - 60)/(80 - 60) = 0.75
STEP 2. Interpolated Z-values at Y = 75 (given Z-values at Y = 60 and Y = 80) become:

Y ↓ X →	1	100	200	300	400	500	600	700
60 Z →	10	59	98	128	149	163	171	174
80 Z →	11	66	110	144	169	187	197	203
75 Z →	10.75 *	64.25 **	107.00	140.00	164.00	181.00	190.50	185.75

* 10.75 = 10 + 0.75 × (11 - 10)
** 64.25 = 59 + 0.75 × (66 - 59)

STEP 3. Search Z = 172.67 lies between Z = 164.00 and Z = 181.00. Z-fraction = (172.67 - 164)/(181 - 164) = 0.51
STEP 4. Interpolated X-value at Z = 172.67 (given X-values at Z = 164 and Z = 181) becomes: 400 + 0.51 × (500 - 400) = 451.00 (final result)

75 Z →	164.00	181.00	172.67
Y ↑ X →	400	500	451.00

Z-values are now "treated like" a transposed y-axis...
...and if X would have had multiple rows, then the function would bring interpolated values for all X rows.

Without further explanation: STEPS 3+4 (vertically) are 'similar' to STEPS 1+2 (horizontally). Having a flexible function able to do STEPS 1+2 (horizontally or vertically) is enough for reverse interpolation. In fact, with such function you can also do regular bilinear interpolation, as can be seen at the bottom of the Excel demo file.

VBA UDF code to be put in a module (alternative code found further below)

In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the code below into the module. The versatile helper function interpolatevalues can be used for linear interpolation, bilinear interpolation, reverse linear interpolation, and reverse bilinear interpolation.

reverseinterpolateZY and reverseinterpolateZX plus helper function interpolatevalues and readandrepairArray

Public Function reverseinterpolateZY(ByVal y As Double, ByVal z As Double, ByVal XRange As Variant, ByVal YRange As Variant, ByVal ValueTable As Variant) As Variant
    reverseinterpolateZY = interpolatevalues(z, interpolatevalues(y, YRange, ValueTable), XRange)
End Function

Public Function reverseinterpolateZX(ByVal x As Double, ByVal z As Double, ByVal XRange As Variant, ByVal YRange As Variant, ByVal ValueTable As Variant) As Variant
    reverseinterpolateZX = interpolatevalues(z, interpolatevalues(x, XRange, ValueTable), YRange)
End Function

Public Function interpolatevalues(searchvalue, searchrange, valuerange) As Variant
    
    Dim searchrangeARR As Variant, valuerangeARR As Variant
    Dim LBV As Double, UBV As Double, LB As Double, UB As Double, INTP_FRACTION As Single
    Dim i As Long
    Dim interpolated() As Double
    
    searchrangeARR = readandrepairArray(searchrange)
    valuerangeARR = readandrepairArray(valuerange)
        
    LBV = WorksheetFunction.XLookup(searchvalue, searchrangeARR, searchrangeARR, , -1)
    UBV = WorksheetFunction.XLookup(searchvalue, searchrangeARR, searchrangeARR, , 1)
    If (UBV = LBV) Then INTP_FRACTION = 0 Else INTP_FRACTION = (searchvalue - LBV) / (UBV - LBV)
    LB = WorksheetFunction.XMATCH(searchvalue, searchrangeARR, -1)
    UB = WorksheetFunction.XMATCH(searchvalue, searchrangeARR, 1)
    
    If (UBound(searchrangeARR, 1) <= UBound(searchrangeARR, 2)) Then
        ReDim interpolated(1 To UBound(valuerangeARR, 1), 1 To 1)
        For i = 1 To UBound(valuerangeARR, 1)
            interpolated(i, 1) = valuerangeARR(i, LB) + INTP_FRACTION * (valuerangeARR(i, UB) - valuerangeARR(i, LB))
        Next
    Else
        ReDim interpolated(1 To 1, 1 To UBound(valuerangeARR, 2))
        For i = 1 To UBound(valuerangeARR, 2)
            interpolated(1, i) = valuerangeARR(LB, i) + INTP_FRACTION * (valuerangeARR(UB, i) - valuerangeARR(LB, i))
        Next
    End If
    interpolatevalues = interpolated
    
End Function

Function readandrepairArray(R)

readandrepairArray = R 'if R=Range then it becomes R.value by default, if R=array then it remains as is
    On Error GoTo repair_second_dimension
    If (UBound(readandrepairArray, 2) > -1) Then Exit Function

repair_second_dimension:
    'repair 2nd dimension - Excel has the nasty habit of converting a single row 2D-array into a 1D-array
    ReDim R_repaired(1 To 1, 1 To UBound(R))
    Dim i As Long
    For i = 1 To UBound(R)
        R_repaired(1, i) = R(i)
    Next i
    readandrepairArray = R_repaired
End Function

LAMBDA functions to be copied in via Name Manager

Dependent on your Excel setting you need to use semicolon or colon in formulas. Choose your setting.

Comma

Semicolon

L_interpolatevalues (helper function, bringing interpolated value(s) as described under step 1+2)
L_reverseinterpolateZX (main function, bringing Y, given Z and X)
L_reverseinterpolateZY (main function, bringing X, given Z and Y)

Alternative VBA UDF code

This alternative code is not multi-purpose, but more "straightforward", and works without helper functions. This code is not found in demo file (as you cannot have two functions with the same name), but you can simply replace all code.

reverseinterpolateZY and reverseinterpolateZX (alternatives)

Public Function reverseinterpolateZY(ByVal y As Double, ByVal z As Double, ByVal XRange As Range, ByVal YRange As Range, ByVal ValueTable As Range) As Variant
    
    If (y < WorksheetFunction.Min(YRange)) Or (y > WorksheetFunction.Max(YRange)) Then
        'reverseinterpolateZY = "Failure 4: y-value input not found within table Yrange"
        reverseinterpolateZY = CVErr(xlErrNA)
        Exit Function
    End If
    
    Dim Y1, Y2, PY1, PY2, FY, FZ
    
    'Determine data table points around searchvalues
    Y1 = WorksheetFunction.XLookup(y, YRange, YRange, , -1)
    Y2 = WorksheetFunction.XLookup(y, YRange, YRange, , 1)
    
    PY1 = WorksheetFunction.Match(Y1, YRange, 0)
    PY2 = WorksheetFunction.Match(Y2, YRange, 0)
    
    'Y fraction
    If (Y2 = Y1) Then FY = 0 Else FY = (y - Y1) / (Y2 - Y1)
    
    'Create interpolation values for X
    Dim C As Range
    Dim i As Long
    Dim Xinterpolated() As Double
    i = 1
    For Each C In XRange
        ReDim Preserve Xinterpolated(i)
        Xinterpolated(i) = ValueTable.Cells(PY1, i) + FY * (ValueTable.Cells(PY2, i) - ValueTable.Cells(PY1, i))
        
        'Search for Z that lies between Xinterpolate(i) and  Xinterpolate(i+1)
        If (i > 1) Then
            If WorksheetFunction.Median(Xinterpolated(i - 1), Xinterpolated(i), z) = z Then
            
                'Z fraction
                If (Xinterpolated(i) = Xinterpolated(i - 1)) Then FZ = 0 Else FZ = (z - Xinterpolated(i - 1)) / (Xinterpolated(i) - Xinterpolated(i - 1))
                    
                reverseinterpolateZY = XRange.Item(i - 1) + FZ * (XRange.Item(i) - XRange.Item(i - 1))
                Exit Function
            End If
        End If
        i = i + 1
    Next
    
    'Else error
    'reverseinterpolateZY = "Failure 1: your z-value of " & z & " does not lie in the z-range " & Xinterpolated(1) & " (at x = " & XRange.Item(1) & ") and " & Xinterpolated(i - 1) & " (at x = " & XRange.Item(i - 1) & ")"
    reverseinterpolateZY = CVErr(xlErrNA)
      
End Function

Public Function reverseinterpolateZX(ByVal x As Double, ByVal z As Double, ByVal XRange As Range, ByVal YRange As Range, ByVal ValueTable As Range) As Variant
    
    If (x < WorksheetFunction.Min(XRange)) Or (x > WorksheetFunction.Max(XRange)) Then
        'reverseinterpolateZX = "Failure 4: x-value input not found within table Xrange"
        reverseinterpolateZX = CVErr(xlErrNA)
        Exit Function
    End If
    
    Dim X1, X2, PX1, PX2, FX, FZ
    
    'Determine data table points around searchvalues
    X1 = WorksheetFunction.XLookup(x, XRange, XRange, , -1)
    X2 = WorksheetFunction.XLookup(x, XRange, XRange, , 1)

PX1 = WorksheetFunction.Match(X1, XRange, 0)
    PX2 = WorksheetFunction.Match(X2, XRange, 0)
    
    'X fraction
    If (X2 = X1) Then FX = 0 Else FX = (x - X1) / (X2 - X1)
    
    'Create interpolation values for Y
    Dim C As Range
    Dim i As Long
    Dim Yinterpolated() As Double
    i = 1
    For Each C In YRange
        ReDim Preserve Yinterpolated(i)
        Yinterpolated(i) = ValueTable.Cells(i, PX1) + FX * (ValueTable.Cells(i, PX2) - ValueTable.Cells(i, PX1))
        
        'Search for Z that lies between Xinterpolate(i) and  Xinterpolate(i+1)
        If (i > 1) Then
            If WorksheetFunction.Median(Yinterpolated(i - 1), Yinterpolated(i), z) = z Then
            
                'Z fraction
                If (Yinterpolated(i) = Yinterpolated(i - 1)) Then FZ = 0 Else FZ = (z - Yinterpolated(i - 1)) / (Yinterpolated(i) - Yinterpolated(i - 1))
                    
                reverseinterpolateZX = YRange.Item(i - 1) + FZ * (YRange.Item(i) - YRange.Item(i - 1))
                Exit Function
            End If
        End If
        i = i + 1
    Next
    
    'Else error
    'reverseinterpolateZX = "Failure 1: your z-value of " & z & " does not lie in the z-range " & Yinterpolated(1) & " (at y = " & YRange.Item(1) & ") and " & Yinterpolated(i - 1) & " (at y = " & YRange.Item(i - 1) & ")"
    reverseinterpolateZX = CVErr(xlErrNA)
      
End Function