Excel Lookup nth Occurrence/Instance
Category: [Excel] Demo Available
Excel Lookup nth Occurrence/Instance
Got any Excel/VBA Questions? Free Excel Help
This UDF will look in the first column in a Table or Range for the N'th occurrence of a specified value, then look in a nominated column for another specified value on the same row. It will then return the result from the same row in a specified column.
The Code:
Function FindNth(Table As Range, Val1 As Variant,Val1Occrnce As Integer, _
Val2 As Variant,Val2Col As Integer, ResultCol As Integer)
'''''''''''''''''''''''''''''''''''''''
'Written by OzGrid Business Applications
'www.ozgrid.com
'Finds the N'th value in the first Column of a table that has a stated _
value on the same row in another Column.
'''''''''''''''''''''''''''''''''''''''
Dim i As Integer
Dim iCount As Integer
Dim rCol As Range
For i = 1 To Table.Rows.Count
If Table.Cells(i, 1) = Val1 And _
Table.Cells(i, Val2Col) = Val2 Then
iCount = iCount + 1
End If
If iCount = Val1Occrnce Then
FindNth = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function
To use this function, push Alt+F11 and go to Insert>Module. Paste in the code, push Alt+Q and save. Use the function as shown in graphic example. You can access it under "User Defined" in the Paste Function (Shift+F3).
| Type | Amount | Sex | Cost | Purchased |
| Cat | 1 | Male | $ 5.00 | 22/05/01 |
| Cat | 2 | Male | $ 5.00 | 15/00/00 |
| Dog | 3 | Male | $ 20.00 | 25/04/00 |
| Rat | 5 | Female | $ 1.00 | 15/08/01 |
| Mouse | 1 | Female | $ 1.00 | 18/02/01 |
| Bird | 5 | Male | $ 10.00 | 1/05/01 |
| Horse | 1 | Female | $ 100.00 | 2/04/00 |
| Dog | 2 | Male | $ 150.00 | 15/06/99 |
| Horse | 8 | Male | $ 200.00 | 14/05/00 |
| Cat | 9 | Female | $ 100.00 | 15/05/99 |
| Dog | 6 | Male | $ 200.00 | 22/08/01 |
| Formula | =FindNth(A1:E12,"Dog",2,"Male",3,5) | |||
| Result | 15/06/99 | |||
The Table range is A1:E12
"Dog" is the value to find in the first column of A1:E12
2 is the occurrence of "Dog" to find in the first Column of A1:E12
"Male" is second value to find
3 is the relative position of the column in A1:E12 to find "Male"
5 is the relative position in the column to return our result from
In other words, find the second occurrence of a Male Dog and return the result from the same row in the fifth column. It is important that the code is case sensitive. This can be overcome by using:
Option Compare Text
Placed at the top of the module.
See also:
| Index to Excel VBA Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.