Lookup function

finnrichard

New Member
Joined
Sep 12, 2014
Messages
2
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
This tweaked version is working for me to return the "position" in the table of the Nth occurrence:

Code:
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

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

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

Code:
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
 
Last edited:

finnrichard

New Member
Joined
Sep 12, 2014
Messages
2
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



Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,913
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top