Created by Myrna Larson

An UDF that returns values “between” the points in the lookup table.

Function InterpolateVLOOKUP(x As Single, Table As Range, _
   YCol As Integer)
    ‘Returns the interpolated number from the first column of
   ‘Table and the YCol column of table.
   ‘x – the number between two numbers in the first column of Table
   ‘Table – The range of values with at least YCol columns
   ‘YCol – the column of Table used for the Y-side of the interpolation
    ‘Dim TableRow As Integer, Temp As Variant
   Dim TableRow As Long, Temp As Variant ‘see below
   Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double
    Dim d As Double
    ‘TableRow – the row of Table that is less than or equal to x
   ‘x0, x1, y0, y1 – the coordinates to interpolate
   ‘d – Your guess is as good as mine
    ‘skip the error when match returns N/A
   On Error Resume Next
    ‘Find the row in Table that’s less than or equal to x
   Temp = Application.WorksheetFunction.Match(x, Table.Resize(, 1), 1)
    ‘If Match returns an error, return the error
   If IsError(Temp) Then
        InterpolateVLOOKUP = CVErr(Temp)
        ‘TableRow = CInt(Temp)
       ‘If your table has more than 32,767 rows, this will fail, so
       ‘it should be CLng.  I don’t know how Temp could be anything
       ‘but a Long Integer, though.
       TableRow = Temp
        ‘The first coordinates are the from the row just less than x
       x0 = Table(TableRow, 1)
        y0 = Table(TableRow, YCol)
        ‘If x is the same as the row just less than (or equal to) x,
       ‘return the value from YCol, just like a normal VLOOKUP
       If x = x0 Then
            InterpolateVLOOKUP = y0
            ‘Get the second coordinates from the next row
           x1 = Table(TableRow + 1, 1)
            y1 = Table(TableRow + 1, YCol)
            ‘Do the math
           InterpolateVLOOKUP = (x – x0) / (x1 – x0) * (y1 – y0) + y0
        End If
    End If
End Function

Editor’s Note: Distracting comments and code modifications by me. Here’s an example:

range showing function use

Eight is between 7 and 9, so it returns 10 which is between 9 and 11.

Posted in Uncategorized

17 thoughts on “VLOOKUP Between

  1. Interesting–I’m sure there was an application in mind when it was created, so blame my lack of imagination, but I can’t think of anything I’d use this for. Can you give me an idea?

  2. Myrna’s UDF is definitely cleaner than:


  3. The regular lookup is a step function, which is great for looking up tax rates or commissions. But there are many reasons to want a smoother function. Suppose a surveyor measured your yard, and determined a profile along one edge of the property which looked like this (don’t know how it will look in proportional ascii):

    D=Distance from corner of lot (ft)
    H=Height above corner of lot (ft)

    D H
    0 0
    10 0
    20 4
    30 4

    It’s a trivial example, but suppose you wanted to know the height at 15 feet. It’s flat from 0 to 10 ft, rises to 4 feet high over the next 10 feet, then is flat at 4 ft high for another 10 feet. VLOOKUP tells me that at 15 feet, the height is zero, while making a simple XY chart and manually interpolating shows it’s obviously not zero. I didn’t check Dick’s rendering of Myrna’s code, but I assume it will agree with the chart that at 15 feet from the corner, the terrain is 2 feet high.

  4. It’s also possible to use an “InterpolateLookup” type formula for this purpose:


  5. if you are just using linear approximation, would it be more accurate to use the FORECAST function?

  6. Simon: No. FORECAST creates a regression line taking all the data into account, not a piecewise linear interpolation. To do the latter one could use
    where MatchIdx is =MATCH(targetVal,XVals,1)

    Comments on the original code:

    It might be safer to test abs(x-x0)

  7. I am trying to delete all rows in a data set that have a cell in the row containing an error (#N/A). Can anyone help with some code that would enable me to do this?

  8. I worked out something quite similar when I had heat rates (fuel efficiencies for power plants) for specified levels of output, but I needed heat rates at intermediate levels as well. Interesting to note the ultimate source…

    Option Explicit

    Function Interpolate(X As Double, XRange As Range, YRange As Range, Optional InterpType As Integer = 0) As Double

    ‘ Function returns interpolated value of “Y” for supplied X, based on supplied ranges of known
    ‘ X and Y values. Optional “InterpType” argument controls how function responds to X arguments
    ‘ outside the known X values:
    ‘ If InterpType is:
    ‘ = 0: returns an error (default)
    ‘ = 1: extrapolates based on last two X-Y pairs (either two highest or two lowest)
    ‘ = 2: extrapolates based on first and last X-Y pair (full range of supplied values)
    ‘ = 3: extrapolates based on first or last X-Y pair and the origin (0-0)
    ‘ Other values return an error

    ‘ Function is based on the “InterpolateVLOOKUP” UDF developed by Myrna Larson, and published in the
    ‘ Excel Expert’s E-Letter, which can be found at: http://www.j-walk.com/ss/excel/eee/eee002.txt

    Dim blErr As Boolean
    Dim iBase As Integer
    Dim iComp As Integer
    Dim i As Integer
    Dim dX0 As Double
    Dim dX1 As Double
    Dim dY0 As Double
    Dim dY1 As Double
    Dim Temp As Variant

    On Error Resume Next

    ‘ ensure that XRange is sorted ascending so match function result is reliable
    For i = 1 To XRange.Count – 1
    If XRange(i + 1) = max(XRange)
    Select Case InterpType
    Case 0
    If X XRange(XRange.Count) Then ‘X is IS > highest value in range – represents an error
    blErr = True
    End If
    Case 1
    iComp = iBase – 1
    Case 2
    iComp = 1
    Case 3
    iComp = 0
    Case Else
    blErr = True
    End Select
    Case Else
    ‘match returned position of value next larger than X, within XRange
    iComp = iBase + 1
    End Select
    dX0 = XRange(iBase)
    dY0 = YRange(iBase)
    If X = dX0 Then
    Interpolate = dY0
    dX1 = XRange(iComp)
    dY1 = YRange(iComp)
    End If
    If blErr = True Then
    Err.Raise Number:=11
    Interpolate = CVErr(Err)
    ‘return “div0? error, to ensure that error propagates through s/sheet calculations
    Interpolate = (X – dX0) / (dX1 – dX0) * (dY1 – dY0) + dY0
    End If
    End If

    End Function

  9. I am looking for a excel function that will interpolate from a 3-d table with two inputs.

    Could you help?



  10. I am completely new to UDFs and I’m trying to use your functions. When I try to use the function by Myrna Larson I get #VALUE! in the cell, where I would like to be recieving the interpolated value. When I use the function by dcardno I get #VALUE! and and error. Once back into Microsoft Visual Basic the top function line is highlighted yellow with an error to the left. Any help would be greatly appreciated. Such a function would be highly useful to me.


  11. David – Myrna’s code works for me. To step through the code you can set a break point in the code by left-clicking in the left margin next to any executable statement (ie not a Dim, comment or blank line). You should get a brown circle marking the break point. Then go back to the worksheet, select a cell containing the UDF you want to check, press F2 then enter. You should be taken to the line of code where you inserted the break point. Now press F8 to step through the code one line at a time, and you should be able to find the problem. Don’t forget to clear the break point by clicking on it, or use the debug menu, clear all breakpoints.

    The dcardno code seems to have lost some lines when it was pasted here. That sometimes happens.

  12. The Range() indexing is backwards. Anything that looks like Range(column, row) should be rewritten as Range(row, column), eg:

    ‘The first coordinates are the from the row just less than x
    x0 = Table(1, TableRow)
    y0 = Table(YCol, TableRow)

    rather than:

    ‘The first coordinates are the from the row just less than x
    x0 = Table(TableRow, 1)
    y0 = Table(TableRow, YCol)
  13. Tushar Mehta’s solution as provided in his web site worked as charm. 2D interpolation is not a problem anymore.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.