Thanks Bakerman, This post related to my other thread regarding the loop, I tried to perform a vlookup as above but then decided to do a workbookfunction.vlookup within loop:
Code:
On Error Resume Next
   Dim result As Variant
For Each oCell In Range(Range("A2"), Range("A1000").End(xlUp))
    result = Application.VLookup(oCell, Workbooks("Shipment Report " & Year(GetDateRangeTo) & ".xls").Sheets("Weekly Shipped Details") _
    .Range("I5:I1000").Cells, 1, 0)
        oCell.Offset(0, 2).Select
        Selection.Value = result
Next