Category: [Excel]  Demo Available 

---

The Ultimate Excel Lookup Function

Got any Excel/VBA Questions? Excel Help

 

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.

SYNTAX

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)

1. Where "Project 1" is the Find_Val.

2. 2 is the Occurrence.

3. $A$1:$D$9 is the Table_Range.

4. 3 is the Offset_Cols

5. 1 is the optional Column_Lookin

6. -1 is the optional Row_Offset

 

See also:

Index to Excel VBA Code

Show/Hide a Custom Toolbar & Remove/Restore Excel's Toolbars

Track/Report User Changes on an Excel Worksheet/Workbook

Transfer Multi-Select ListBox To Range Of Cells

Transpose Rows Into Columns

Excel Lookup nth Occurrence/Instance

Add Excel UDF/Custom Function to a Category & Add a Description

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Gallery

---

• 
• 
• 
• 
• 
•