Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Lookup nth Occurrence/Instance


Back to: Excel Custom Function/Formulas . 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


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

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software


Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates