XL 2013 Remplacer Vlookup et Hlookup par une fonction vba

Bonjour tout le monde,
je débute sur vba, et j'aimerai simplifier voir remplacer les fonctions Hlookup et Vlookup.
Pour le moment j'ai écrit cela de la manière suivante mais cela ne me convient pas vraiment.
L'un de vous pourrait il m'aider a simplifier cette écriture .

Merci de votre aide.
Hm

If Not Intersect(Target, Range("I1")) Is Nothing Then

With Sheets("template")

.Range("A5").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 2, 0), "")
.Range("A6").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 3, 0), "")
.Range("A7").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 4, 0), "")
.Range("A8").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 5, 0), "")
.Range("A9").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 6, 0), "")
.Range("A10").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 7, 0), "")
.Range("A11").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 8, 0), "")
.Range("A12").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 9, 0), "")
.Range("A13").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 10, 0), "")
.Range("A14").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 11, 0), "")
.Range("A15").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 12, 0), "")
.Range("A16").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 13, 0), "")
.Range("A17").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 14, 0), "")
.Range("A18").Value = Application.WorksheetFunction.IfError(Application.HLookup(.Range("I1").Value, Sheets("BOM").Range("A1:X100"), 15, 0), "")
End With



With Sheets("template")

.Range("B5").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A5").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B6").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A6").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B7").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A7").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B8").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A8").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B9").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A8").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B10").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A10").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B11").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A11").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B12").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A12").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B13").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A13").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B14").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A14").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B15").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A15").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B16").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A16").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B17").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A17").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")
.Range("B18").Value = Application.WorksheetFunction.IfError(Application.VLookup(.Range("A18").Value, Sheets("mat_prem").Range("A:E"), 5, 0), "")



End With


End If