Category: [Excel]  Demo Available 

---

Lookup Any Occurrence in Any Table Column

Return The Corresponding Cell to the Left or Right

 

Got any Excel Questions? Free Excel Help

 

Excel is rich in lookup functions but lacks if you need to lookup the nth occurrence and return the corresponding cell to the left or right of the match.

Try this Custom Function, which should work in Excel 2000 or above. 

Used in any cell like;
=Lookup_Occurrence("Jan",$A$1:$C$5000,2,-1,3)

Function Lookup_Occurence(To_find, Table_array As Range, _
    Look_in_col As Long, Offset_col, Occurrence As Long, _
    Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
    
    
    'Written by www.ozgrid.com
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''Custom Function (http://www.ozgrid.com/VBA/Functions.htm) _
    ''''''''to lookup the nth occurrence (in part or in whole and can _
    ''''''''be case sensitive) in the 1st column of an table _
    ''''''''array and return the corresponding cell x columns to the _
    ''''''''right OR left.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     
    Dim lLoop As Long
    Dim rFound As Range
    Dim xlLook As XlLookAt
    Dim lOcCheck As Long
     
     
     
    If Part_cell_match = False Then
        xlLook = xlWhole
    Else
        xlLook = xlPart
        To_find = "*" & To_find & "*"
    End If
     
    Set rFound = Table_array.Columns(Look_in_col).Cells(1, 1)
    
    
    On Error Resume Next
     lOcCheck = WorksheetFunction.CountIf _
        (Table_array.Columns(Look_in_col), To_find)
        
        If lOcCheck < Occurrence Then
           Lookup_Occurence = vbNullString
        Else
            For lLoop = 1 To Occurrence
                Set rFound = Table_array.Columns(Look_in_col).Find _
                (What:=To_find, After:=rFound, LookAt:=xlLook, LookIn:=xlValues, _
                    MatchCase:=Case_sensitive)
            Next lLoop
     
            On Error GoTo 0
            Lookup_Occurence = rFound.Offset(0, Offset_col)
    End If
     
End Function

Used in any cell like;
=Lookup_Occurrence("Jan",$A$1:$C$5000,2,-1,3)

Where;
Jan is the value to find. (To_find)

$A$1:$C$5000 is data table AND A1:C1 has headings. (Table_array)

2 is the column (relative to Table_array) of the data table to look in, column B in this case. (Look_in_column)

-1 is the offset from the found occurrence (Column A in this case). It can be positive or negative. (Offset_column)

3 is the occurrence of Jan to find in Column B. (Occurrence)

Or, if you use TRUE, TRUE  (the default if omitted is FALSE, FALSE) for the last 2 optional arguments (Case_sensitive and Part_cell_match) it becomes case sensitive and looks for part cell matches. That is, a cell housing Jan. Is the next month. or The next month is Jan. or Next month is Jan, followed by Feb, will all be considered matches.

If the number of occurrences (based on your arguments) is not possible, the Function returns empty text.

 

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

---

• 
• 
• 
• 
• 
•