Guru do Excel e Power BI

Public Function PROCVN(Val1 As Variant, _
                       Table As Range, _
                       ResultCol As Integer, _
                       Optional Val1Occrnce As Integer = 1)
    Dim i As Integer
    Dim iCount As Integer
    Dim rCol As Range
        For i = 1 To Table.Rows.Count
        If Not (Application.WorksheetFunction.IsError(Table.Cells(i, 1))) Then
            If UCase(Table.Cells(i, 1)) = UCase(Val1) Then
                iCount = iCount + 1
            End If
            If iCount = Val1Occrnce Then
                PROCVN = Table.Cells(i, ResultCol)
                Exit For
                Else
                PROCVN = CVErr(xlErrNA)
            End If
        End If
        Next i
End Function

Public Function PROCVN(Val1 As Variant, _
                       Table As Range, _
                       ResultCol As Long, _
                       Optional Val1Occrnce As Long = 1)
                       
    Static vrtDados     As Variant
    Dim i               As Long
    Dim iCount          As Long
    Dim rngInt          As Range
    
    Set rngInt = Application.Intersect(Table, Planilha1.UsedRange)
    If Not VBA.IsArray(vrtDados) Then
        vrtDados = rngInt.Value
    End If
    
        For i = LBound(vrtDados, 1) To UBound(vrtDados, 1)
            If Not (VBA.IsError(vrtDados(i, 1))) Then
                If VBA.UCase(vrtDados(i, 1)) = VBA.UCase(Val1) Then iCount = iCount + 1

                If iCount = Val1Occrnce Then
                    PROCVN = vrtDados(i, ResultCol)
                    Exit For
                Else
                    PROCVN = CVErr(xlErrNA)
                End If
            End If
        Next i
End Function