UDF - Difficulty using INDIRECT

[Lizabeta]

Using the following UDF from Ozgrid (below) for a multiple occurance VLOOKUP.

=Lookup_Occurance(Val,Range,Col,Offset,Occurance)

It will let me use an Indirect Reference for the table range, but not for the Offset, which will be a dynamic number. I get a #Value error if I use Indirect for the offset...

Any advice?

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 [URL="http://www.ozgrid.com"]www.ozgrid.com[/URL]
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ''''''''Custom Function ([URL]http://www.ozgrid.com/VBA/Functions.htm[/URL]) _
    ''''''''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

 

[Jonmo1]

It let's me use indirect for the offset.

Post the formula you used.

 

[Lizabeta]

=Lookup_Occurence(A4,INDIRECT(Q1),1,INDIRECT(P2),2)

The Q1 Indirect refers to the range: 'Resource Schedule'!$D$1:$D$310
The Offset Indirect reference refers to P2, which has the following formula:
O2+1-O2-O2

I need the offset number to always be column A...
The lookup column will be one of anything from 3 to 57.

 

[Lizabeta]

Figured it out... I don't need to use INDIRECT. It will let me reference the cell directly... instead of -3, I can just put in P2