Cette page appartient aux archives web de l'EPFL et n'est plus tenue à jour.
This page belongs to EPFL's web archive and is no longer updated.

Website Stats

# Find nth occurrence in a column

A solution to  find Nth occurence in a column coupled with INDEX/ MATCH function.

Step 1: In Excel, Enable "Developer" tab in Excel:

Pathway (for Mac): Preference--> Ribbons--> select "Developer"

Step 2: Insert Macro

Pathway: Develop--> Editor--> Insert module (copy the green text below)

Function NthInstance(F As String, R As Range, N As Long) As String
Dim vA As Variant
vA = R.Value
For I = LBound(vA, 1) To UBound(vA, 1)
If InStr(1, vA(I, 1), F) > 0 Then
ct = ct + 1
If ct = N Then
Exit Function
End If
End If
Next I
If ct < N Then NthInstance = "Only " & ct & " instances found"
End Function

Credit:  source of the VBA code
http://www.mrexcel.com/forum/excel-questions/678792-find-nth-occurrence-column.html

Step 3: INDEX/INDIRECT function can then be applied to find values of Nth occurencies.

e.g.

=INDEX(\$F\$1:\$H\$10,ROW(INDIRECT(NthInstance(\$F29,\$F\$2:\$F\$10,\$E29))),2)