Skip to main content

NthMatch UDF for getting Row No. of the Nth Match of a Value in a Range


















NthMatch  is Custom UDF made by me to overcome the cons of Match Function and Array formulas.
NthMatch function provides the row no. of the Nth match in a range, say in the given example, it is providing the Row No ie. 7 for the third Match of "Sachin" in the Range. The Reason I made this formula is that the Match Function provides only the first match and also it needs a lot to skill for a lay man to write an array formula to do the same job. As you can see in the above picture, Both functions gives the same results but the NthMatch syntax is much shorter and easier to understand than array formula.

Syntax:-
=NthMatch(Lookup_Value, Lookup_Range, Match No.)

Here are the codes:-
Ist Code:-


Function NthMatchE(ByVal Lvalue As Variant, ByVal Lrange As Variant, Mnum As Variant)
f = "SMALL(IF(" & Lrange.Address & "=""" & Lvalue & """,ROW($1:" & Lrange.Rows.Count & "))," & Mnum & ")"
NthMatchE = Evaluate(f)
End Function

2nd Code:-

       
Function NthMatch(ByVal Lvalue As Variant, Lrange As Variant, Mnum As Variant)
    Mstr = "|" & Join(Application.Transpose(Lrange), "|")
    Lvalue = "|" & Lvalue
    Start = 0
    
    For c = 1 To Mnum
        Start = InStr(Start + 1, Mstr, Lvalue)
        If Start = 0 Then NthMatch = "#N/A": GoTo 0     'This the line which I have included to leave loop.
    Next

    Temp = Left(Mstr, Start)
    NthMatch = Len(Temp) - Len(Replace(Temp, "|", ""))  'Calculating Row No.
0
End Function


The First one is Faster of the two. Because it is as good as making same array formula run through VBA. The second one is just for the Knowledge base that how it can be achieved using String Manipulation.
I wanna mention here that Array alternative is faster than all but using Evaluate function with a UDF makes it more understandable and easy to use. If you know Arrays then I will prefer going with them.

Regards,
Vikas Gautam
Labels:

Comments

Post a Comment

Popular posts from this blog

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more