Using Index match in VBA returns wrong value

[orangebloss]

Hi

I thought I'd managed to work out how to do an index match in VBA and it does work...to an extent.... For some reason it's returning the index value for the Activecell(-1,-3) rather than the Activecell(0,-3) and I'm getting a lot of #N/A values even when the data is available

I've tried named ranges, using the whole column, specifying a subset of the data to check but still getting the error. Can anyone suggest why this might be happening? Or a better way to produce the Index match?

VBA code is below but what I'm trying to replicate is the following Index match statement

=IF(TDATE>0,INDEX(TECHDEPT,MATCH(RPCHECK,RPNAME,0)),"")

where
TDATE = Activecell.offset(0,-12)
TECHDEPT= Sheets("Total Hours Booked").Range("L:L")
RPCHECK = Application.match(ActiveCell(0, -3)
RPNAME= Sheets("Total Hours Booked").Range("A:A")

Sub updated_tech_dept() 
    Application.ScreenUpdating = False 
    Worksheets("MODTRACKER DATA").Select 
    Range("AF2").Select 
     
    Do While ActiveCell.Offset(0, -12).Value <> "" 
        ActiveCell.Value = Application.Index(Sheets("Total Hours Booked").Range("L2:L2000"), Application.match(ActiveCell(0, -3), Sheets("Total Hours Booked").Range("A2:A2000"), 0)) 
        ActiveCell.Offset(1, 0).Select 
    Loop 
    MsgBox ("All Complete") 
    Application.ScreenUpdating = True 
End Sub

Thanks in advance