If you need macro for your problem, I don't know that but I could help you with formulas that will return the values that you need in column D of sheet 1.
=IFERROR(INDEX(Sheet2!D$2
$8,MATCH(A2,Sheet2!A$2: A$8,0)),"")
OR
=IFERROR(VLOOKUP(A2,Sheet2!A$2:H$8,4,FALSE),"")