FINDNTH Excel User-Defined Function (UDF)
Find the nth occurrence of a value in a worksheet column or row


Doug Fisher


Microsoft Excel's MATCH function is great for locating the first instance of a number or string in a row or column, but what if the need arises to locate instances beyond the first? What if the 2nd, 3rd, or nth instance is required? There are formulas out there that will do the trick, but sometimes it might be better to have a single function available versus a formula composed of multiple functions. That is where FINDNTH comes in. It is a user-defined function (UDF) that can become immediately accessible within your Excel workbook by simply copying code (located at the bottom of this page) and pasting it into a VBA module.

Unlike other similar UDFs available on the web, this function works with either columns or rows and has extended functionality built in for specifying the match type as well as the return of either the position, the row/column, or the value of the nth match. The latter may prove helpful if the match type 'contains' the value you are looking for and you wish to see the full value, e.g., the nth instance may contain the value searched for, "10", but be "1100".

FINDNTH Formula Format:

=FINDNTH ( range, instance, search_value, match_type, return )

Range: The single row or single column range where the nth position of a specified value is being looked up.
Instance: The nth instance of an unspecified value or a specified Search_Value.
Search_Value: The value being looked up within the row or column.
                (Optional. Without a search_value, it will return the nth instance of non-blanks or blanks if match_type is included and set to 1)
Match_Type: Specifies whether values are found that are exact matches or values containing the search_value and if the case should match.
                0 = contains search_value, 1 = matches search_value, 2 = contains search_value and matches case, 3 = matches search_value and case.
                (Optional. Without a match_type specified, it defaults to 0 and returns all values containing the search_value searched regardless of case.)
Return: Specifies whether the position (row or column number) or the value of the nth instance should be returned.
                0 = position, 1 = row or column number, 2 = cell value
                (Optional. Without a Return specified, it defaults to 0 and returns the position of the nth instance within the range. This allows FINDNTH to 
                be used in tandem with the INDEX function in the same manner as MATCH is often used.)

In the image below you can see the various results that can be obtained using the FINDNTH function. The formulas are listed beneath along with the range of cells each is pasted into. The search_value in this example is "Tom" located in $C$4 and the range being searched is $B$4:$B$21.

(Note that the ROW() function in these formulas is used to generate a sequence from 1 to 18 accommodating each cell within the range. It does this in each column by subtracting a value that is one less than the row value in the first row. Thus row 4 = ROW()-3 =1. ROW()-3 in row 5 will then generate 2, row 6 will generate 3, etc. COLUMN()-x can similarly be used if the data is transposed horizontally.)

1
1. All non-blanks (16 results)
E4:E21 =FINDNTH($B$4:$B$21,ROW()-3)       returns 1 as the range position for the 1st instance
F4:F21 =FINDNTH($B$4:$B$21,ROW()-3,,,1)       returns 4 as the row number for the 1st instance
G4:G21 =FINDNTH($B$4:$B$21,ROW()-3,,,1)       returns 'To' as the value for the 1st instance

2. All blanks (2 results)
I4:I21 =FINDNTH($B$4:$B$21,ROW()-3,,1)        returns 7 as the range position for the 1st instance
J4:J21 =FINDNTH($B$4:$B$21,ROW()-3,,1,1)       returns 10 as the row number for the 1st instance
K4:K21 =FINDNTH($B$4:$B$21,ROW()-3,,1,2)       returns ' ' as the value for the 1st and every following instance

3. Contains value (11 results)
M4:M21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4)        returns 2 as the range position for the 1st instance
N4:N21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,0,1)       returns 5 as the row number for the 1st instance
O4:O21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,0,2)       returns 'ATOM' as the value for the 1st instance

4. Exact value
(6 results)
Q4:Q21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,1)        returns 4 as the range position for the 1st instance
R4:R21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,1,1)       returns 7 as the row number for the 1st instance
S4:S21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,1,2)       returns 'TOM' as the value for the 1st instance

5. Contains value w/matching case (4 results)
U4:U21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,2)        returns 3 as the range position for the 1st instance
V4:V21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,2,1)       returns 6 as the row number for the 1st instance
W4:W21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,2,2)       returns 'ATom' as the value for the 1st instance

6. Exact value w/matching case (2 results)
Y4:Y21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,3)        returns 5 as the range position for the 1st instance
Z4:Z21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,3,1)       returns 8 as the row number for the 1st instance
AA4:AA21 =FINDNTH($B$4:$B$21,ROW()-3,$C$4,3,2)       returns 'Tom' as the value for the 1st and every following instance


If at any time you require assistance using this UDF, please note that the arguments for FINDNTH (or any other Excel function) can be found by placing your cursor on the function's name within your formula and clicking on the function button (fx) just left of the formula bar. A popup window will appear that defines each element of the formula and provides available value options for match_type and return.

2

FINDNTH can be used on either a single-row or single-column range. It will return a #Value! error code if the specified range is more than 1 cell wide in both directions.

To start using the FINDNTH function, open your Excel worksheet and press Alt+F11 to open the Visual Basic editor. Once open, select Insert/Module from the main menu. Copy the complete code listed below and paste it into the module. Close the Visual Basic editor and save the workbook as an xlsm file (or older xls file if this option is not available) so that it is compatible with macros. You can now begin creating formulas in your worksheet using the FINDNTH function.

Public Function FINDNTH(rng As Range, instance As Variant, _
Optional value_search As String = "", _
Optional match_type____0_COntains__1_EXact__2_COcase__3_EXcase As Variant = "", _
Optional return_type____0_position__1_rowcolumn__2_value As Variant = 0) As String
'>>>>>>>>>> Code by Doug Fisher
'Finds nth occurrence in a range (must be SINGLE row or column)
'Place cursor on FINDNTH in formula and click on fx button left of formula bar to view syntax/results
'Syntax FINDNTH(Range,Instance,value search,match contains/exact/w case, return row or value), e.g., FINDNTH($A$1:$A$7,nth,"string",3,1)
'Syntax FINDNTH(Range,Instance) returns only rows/columns with non-blank cells
'Syntax FINDNTH(Range,Instance,,1) returns only rows/columns with blank cells


Dim i As Long
Dim n As Long
Dim c As Long
Dim r As Long
Dim u As Long
Dim v As Variant: v = rng.Value
Dim x As Long: x = 1
Dim y As Long: y = 1
Dim z As String

'range analysis
If UBound(v, 1) > 1 And UBound(v, 2) > 1 Then 'range is not a SINGLE row or column
    FINDNTH = rng.Cells(y, x).Value = 1 / 0 'returns #Value! error code
   Exit Function
ElseIf UBound(v, 1) > 1 Then 'range is single column
    u = 1
    r = rng.Row - 1
    z = "column"
Else 'range is single row
    u = 2
    c = rng.Column - 1
End If

'Loop through range for results
For i = LBound(v, 1) To UBound(v, u)
If z = "column" Then
    y = i
Else
    x = i
End If
    If match_type____0_COntains__1_EXact__2_COcase__3_EXcase = 3 Then
        If v(y, x) = value_search Then
            n = n + 1
            If n = instance Then
                If return_type____0_position__1_rowcolumn__2_value = 2 Then
                    FINDNTH = rng.Cells(y, x).Value
                ElseIf return_type____0_position__1_rowcolumn__2_value = 1 Then
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column
                    End If
                Else
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row - r
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column - c
                    End If
                End If
                    Exit Function
            End If
        End If
    ElseIf match_type____0_COntains__1_EXact__2_COcase__3_EXcase = 2 Then
        If InStr(1, v(y, x), value_search) > 0 Then
            n = n + 1
            If n = instance Then
                If return_type____0_position__1_rowcolumn__2_value = 2 Then
                    FINDNTH = rng.Cells(y, x).Value
                ElseIf return_type____0_position__1_rowcolumn__2_value = 1 Then
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column
                    End If
                Else
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row - r
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column - c
                    End If
                End If
                    Exit Function
            End If
        End If
    ElseIf match_type____0_COntains__1_EXact__2_COcase__3_EXcase = 1 Then
        If UCase(v(y, x)) = UCase(value_search) Then
                n = n + 1
            If n = instance Then
                If return_type____0_position__1_rowcolumn__2_value = 2 Then
                    FINDNTH = rng.Cells(y, x).Value
                ElseIf return_type____0_position__1_rowcolumn__2_value = 1 Then
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column
                    End If
                Else
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row - r
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column - c
                    End If
                End If
                    Exit Function
            End If
        End If
    Else
        If InStr(1, UCase(v(y, x)), UCase(value_search)) > 0 Then
            n = n + 1
            If n = instance Then
                If return_type____0_position__1_rowcolumn__2_value = 2 Then
                    FINDNTH = rng.Cells(y, x).Value
                ElseIf return_type____0_position__1_rowcolumn__2_value = 1 Then
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column
                    End If
                Else
                    If u = 1 Then
                        FINDNTH = rng.Cells(y, x).Row - r
                    ElseIf u = 2 Then
                        FINDNTH = rng.Cells(y, x).Column - c
                    End If
                End If
                    Exit Function
            End If
        End If
    End If
Next i
End Function

Enjoy.

Click here to submit questions and comments.