Excel Lookup nth Occurrence/Instance

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

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

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 special@ozgrid.com 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