Use Native Excel Function Lookup Formula In VBA

[Excel Fox]

Here's how you do it

Sub fndvalinrng()

    Dim rsltrng As Range
    Dim srch As String

    With ThisWorkbook.Worksheets("Sheet1")
        srch = .Cells(2, 1).Value
        Set rsltrng = .Range("F4:F19")
        .Cells(2, 2).Value = _
        Application.Lookup(2 ^ 15, Application.Search(rsltrng, .Range("A2")), rsltrng)
    End With

End Sub