Unable to get VLookup Property of the WorksheetFunction Class

Question

I am trying to use a VLookup table to get the value of the variable Lookup. I am getting an error of "Unable to get the VLookup property of the WorksheetFunction class" when I call the function. I believe where I am going wrong is the numbering of position, position being the column index, I dont know how to adjust the indexes for such a small chart. This is what the table look like. For all the cases in the table, I am checking if the rate_value falls within the parameter. So for < 50 I am looking for is rate_value is a number less than 50 same for 50 To 100, does rate_valuefall in between 50 to 100.

What I am trying to do is for each rate_value I need it to associate with the value in the table. So for instance, if rate_value = 200, then the value of Lookup = 0.4``rate_value is set as a global Double defined in other code that is not relevant.

This is what my code currently looks like.

Dim Lookup As Double, rate_value As Double, sweep_value_min As Long, sweep_value_max As Long 
Lookup = GetRate(rate_value)
Select Case rate_value
    Case Is < 50
        sweep_value_min = rate_value - Lookup
        sweep_value_max = rate_value + Lookup
    Case 50 To 100
        sweep_value_min = rate_value - Lookup
        sweep_value_max = rate_value + Lookup
    Case Is > 100
        sweep_value_min = rate_value - Lookup
        sweep_value_max = rate_value + Lookup
End Select

---

UPDATED:

Function GetRate(rate As Variant) As Double
Dim wbSrc As Workbook, ws As Worksheet, position As Long

Set wbSrc = Workbooks.Open("C:\Users\Documents\LookupTable.xlsx")
Set ws = wbSrc.Worksheets("Rate")
Select Case rate
   Case Is < 50
       position = 2 
       GetRate = WorksheetFunction.VLookup(rate, ws.Range("A1:B4"), position, False)
   Case 50 To 100
       position = 3
       GetRate = WorksheetFunction.VLookup(rate, ws.Range("A1:B4"), position, False)
   Case Is > 100
       position = 4
       GetRate = WorksheetFunction.VLookup(rate, ws.Range("A1:B4"), position, False)
   Case Is = ""
       ErrorMsg = "No rate value. Can be found. Check before running again."
End Select
End Function

Answer 1

Untested but this is one way to do it if your lookup tables follow that same pattern:

Sub Tester()
    Dim Lookup As Double, rate_value As Double, sweep_value_min As Long, sweep_value_max As Long
    '...
    '...
    If IsNumeric(rate_value) Then
        Lookup = GetRate(rate_value)
        sweep_value_min = rate_value - Lookup
        sweep_value_max = rate_value + Lookup
    End If
End Sub
    
Function GetRate(rate As Variant) As Double
    Const WB_PATH As String = "C:\Users\Documents\LookupTable.xlsx"
    Static data As Variant 'only need to read this once....
    Dim r As Long

    If IsEmpty(data) Then 'already read the data?
        Application.ScreenUpdating = False
        With Workbooks.Open(WB_PATH, ReadOnly:=True)
            data = .Worksheets("Rate").Range("A2:B4").Value
            .Close False
        End With
        Application.ScreenUpdating = True
    End If

    If Len(rate) > 0 And IsNumeric(rate) Then
        For r = 1 To UBound(data)
            If CheckSpan(rate, data(r, 1)) Then 'does this meet the criteria?
                GetRate = data(r, 2) 'return the value from that row
                Exit Function
            End If
        Next r
    Else
        'ErrorMsg = "No rate value. Can be found. Check before running again."
    End If
End Function

'does the value `v` fall inside the range specified by `span` ?
Function CheckSpan(v, ByVal span) As Boolean
    Dim arr
    span = Replace(span, " ", "") 'remove any spaces
    'handles cases <=, >=, <, >, X-Y
    Select Case True
        Case span Like "<=#*": CheckSpan = v <= CDbl(Right(span, Len(span) - 2))
        Case span Like ">=#*": CheckSpan = v >= CDbl(Right(span, Len(span) - 2))
        Case span Like "<#*": CheckSpan = v < CDbl(Right(span, Len(span) - 1))
        Case span Like ">#*": CheckSpan = v > CDbl(Right(span, Len(span) - 1))
        Case span Like "*#-#*"
            arr = Split(span, "-")
            CheckSpan = (v >= CDbl(arr(0)) And v <= CDbl(arr(1)))
    End Select
    Debug.Print v, span, CheckSpan
End Function

'for testing CheckSpan
Sub TestCheckSpan()
    CheckSpan 44, "<=50"
    CheckSpan 50, "<=50"
    CheckSpan 100, "<=50"
    CheckSpan 44, "<50"
    CheckSpan 50, "<50"
    CheckSpan 100, "<50"
    CheckSpan 45, "50 - 100"
    CheckSpan 105, "50 - 100"
    CheckSpan 55, "50 - 100"
    CheckSpan 100, "50 - 100"
End Sub