Thread: A more efficient code for custom function

Try running in memory:

Function ValueAtRight(sInp As String) As Variant
    Dim rInp        As Range
    Dim avInp       As Variant
    Dim iRow        As Long

    With Worksheets("NAME2")
        Set rInp = Intersect(.Columns(1), .UsedRange)
    End With

    avInp = rInp.Value2
    
    For iRow = 1 To rInp.Rows.Count
        If InStr(avInp(iRow, 1), sInp) Then
            ValueAtRight = rInp(iRow, 2)
            Exit Function
        End If
    Next iRow
End Function