fn_NMATCH: Find the Nth Occurrence

Overview

Excel VBA UDF

Like MATCH but finds the Nth occurrence of a value in a range. While Excel's MATCH returns only the first match, fn_NMATCH lets you specify which instance you want — the 2nd, 3rd, or any Nth occurrence.

How It Works

lookup_value: The value to search for.
lookup_vector: The range to search within.
instance_num: Which occurrence to return (1st, 2nd, 3rd, etc.).
Returns the position as a single number. Returns #N/A if the Nth occurrence doesn't exist.

Usage

=fn_NMATCH("Apple", A1:A100, 3)    ' Find the 3rd "Apple"

The VBA Code

Function fn_NMATCH(ByVal lookup_value As Variant, _
        ByVal lookup_vector As Range, ByVal instance_num As Variant) As Variant
    ' Returns the Nth occurrence position of lookup_value in lookup_vector
    Dim Arr_Src As Variant: Arr_Src = lookup_vector.Value2
    Dim R_Src As Long: R_Src = UBound(Arr_Src, 1)
    Dim C_Src As Long: C_Src = UBound(Arr_Src, 2)

    Dim Cnt As Long, r As Long, c As Long
    Dim Num_Nth As Long: Num_Nth = CLng(instance_num)

    For r = 1 To R_Src
        For c = 1 To C_Src
            If CStr(Arr_Src(r, c)) = CStr(lookup_value) Then
                Cnt = Cnt + 1
                If Cnt = Num_Nth Then
                    fn_NMATCH = (r - 1) * C_Src + c
                    Exit Function
                End If
            End If
        Next c
    Next r
    fn_NMATCH = CVErr(xlErrNA)
End Function