# OzGrid

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)

'''''''''''''''''''''''''''''''''''''''

'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.

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.

### Gallery

stars (0 Reviews)