Lookup function

[finnrichard]

Dear fellow forum users,

I am trying to make a lookup function in VBA that returns the nth occurence in a table. So far I have written the following code, but it doesn't work. I am new to VBA, can anyone please help me?

Function FindNthOccurence(lookup_value As String, mytable As Range, Nth As Integer) As String
Dim i As Integer
Dim currentdindex As Integer
currentindex = 0
For i = 1 To mytable.Rows.Count
If mytable(i, 1) = lookup_value Then
currentindex = currentindex + 1
If currentindex = Nth Then
FindNthOccurence = currentindex
Next i

 

[jbeaucaire]

This tweaked version is working for me to return the "position" in the table of the Nth occurrence:

Function FindNthOccurence(lookup_value As String, mytable As Range, Nth As Integer) As String
Dim i As Long, currentindex As Long

For i = 1 To mytable.Rows.Count
    If mytable(i, 1) = lookup_value Then
        currentindex = currentindex + 1
        If currentindex = Nth Then
            FindNthOccurence = i
            Exit Function
        End If
    End If
Next i

End Function

 

[ParamRay]

.
.

I prefer this function, which returns the cell address of the n-th occurrence:

Function LOCATION(lookup_value As String, lookup_array As Range, Optional index_num As Long = 1)

    Dim lkp As Range
    Dim ind As Long
    Dim add() As String
    
    ind = 0
    For Each lkp In lookup_array
        If lkp.Value = lookup_value Then
            ind = ind + 1
            ReDim Preserve add(1 To ind)
            add(ind) = lkp.Address
        End If
    Next lkp
    
    If ind = 0 Then
        LOCATION = CVErr(xlErrNA)
    Else
        LOCATION = add(index_num)
    End If
        
End Function

 

[finnrichard]

Thank you. I have made some arrangements but I still get "#NAME?"

I have a list of clients' telephone numbers (column 4) from a certain country (first column in MyTable). I would like to look up the telephone number of the fifth american in the list.
So lookup value is usa, occurrence=5, columnnumber is 4

Function FindNthOccurence(lookup_value As String, mytable As Range, Nth As Integer, columnnumber As Integer) As String
Dim i As Long
Dim currentdindex As Long
currentindex = 0


For i = 1 To mytable.Rows.Count
    If mytable(i, 1).Text = lookup_value Then
    currentindex = currentindex + 1
    If currentindex = Nth Then
    FindNthOccurence = mytable(i, columnnumber)
    Exit Function
End If
End If
Next i
End Function