<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>
Ozgrid, Experts in Microsoft Excel Spreadsheets

Excel Lookup nth Occurrence/Instance

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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

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

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

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

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