I can't figure out what that does... Column B does not have a column 3 columns to its left, active cell.offset(0,-3)

A little out of my league, And it's not tested, so you may have to tweak it. Actually, since I am guessing a lot about your Project, you will definitely have to tweak it, 
Option Explicit

Sub SamT()
Dim rngSheet1_B
Dim rngSheet1_E 'Just guessing as to the column you want the results in
Dim rngTreple_Col1 'Just guessing that "Treple" is in fact a Named Range
Dim rngTreple_Col3

Dim i As Long
Dim x As Long


With Sheets("Sheet1")
rngSheet1_B = Range(.Range("B2"), .Range("B2").End(xlDown)).Value
End With
ReDim rngSheet1_E(UBound(rngSheet1_B))

rngTreple_Col1 = Range("Treple").Columns(1).Value
rngTreple_Col3 = Range("Treple").Columns(3).Value

For i = 1 To UBound(rngSheet1_B)
  x = WorksheetFunction.Match(rngSheet1_B(i), rngTreple_Col1, 0)
  rngSheet1_E(i) = rngTreple_Col3(x)
Next i

Sheets("Sheet1").Range("E").Resize(UBound(rngSheet1_E), 1) = rngSheet1_E

End Sub