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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,959
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top