 |
|
|
|
|
|
Got any Excel Questions? Excel Help
More Custom Functions. SEE ALSO: Vlookup | Vlookup Across Worksheets | 5 Condition Vlookup | 4 Condition Vlookup | 3 Condition Vlookup | 2 Condition Vlookup | How to stop the #N/A! error | Lookup Any Occurrence in Any Table Column || Hlookup Formula || Left Lookup in Excel || Excel Lookup Functions | Multi-Table Lookup
This Custom Function (UDF) will look in any column, unless specified, for the nth occurrence of the specified value and return the corresponding value x rows above or below the found value to the left or right.
OzgridLookup(Find_Val,Occurrence,Table_Range,Offset_Cols, Column_Lookin,Row_Offset)
The last 2 arguments are optional. Any Table_Range should include the 1st row which should be headings.
Function OzgridLookup(Find_Val As Variant, Occurrence As Long, Table_Range As Range, _
Offset_Cols As Long, Optional Column_Lookin As Long, Optional Row_Offset As Long) As Variant
Dim lLoop As Long
Dim FoundCell As Range
If Column_Lookin = 0 Then 'No column # specified
With Table_Range
'Top left cell has Find_Val & Occurrence is 1
If Table_Range.Cells(1, 1) = Find_Val And Occurrence = 1 Then
OzgridLookup = .Cells(1, 1)(1, Offset_Cols + 1)
Exit Function 'All done :)
Else 'No column # specified so search all for _
nth Occurrence reading left to right
Set FoundCell = .Cells(1, 1) 'Set cell variable for Find start
For lLoop = 1 To Occurrence 'Loop as many times as Occurrences _
and each time Set "FoundCell" to start next Find from
Set FoundCell = _
Table_Range.Find(What:=Find_Val, After:=FoundCell, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlRows, SearchDirection:=xlNext)
Next lLoop
End If
End With
Else 'column # specified
With Table_Range.Columns(Column_Lookin) 'Work with column # specified
Set FoundCell = .Cells(1, 1) 'Set cell variable for Find start
For lLoop = 1 To Occurrence 'Loop as many times as Occurrences _
and each time Set "FoundCell" to start next Find from
Set FoundCell = _
Table_Range.Find(What:=Find_Val, After:=FoundCell, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlRows, SearchDirection:=xlNext)
Next lLoop
End With
End If
OzgridLookup = FoundCell.Offset(Row_Offset, Offset_Cols)
End Function
Used in any cell like;
=OzgridLookup("Project 1",2,$A$1:$D$9,3,1,-1)
OzgridLookup(Find_Val,Occurrence,Table_Range,Offset_Cols, Column_Lookin,Row_Offset)
Where "Project 1" is the Find_Val.
2 is the Occurrence.
$A$1:$D$9 is the Table_Range.
3 is the Offset_Cols
1 is the optional Column_Lookin
-1 is the optional Row_Offset
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
GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS