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