Basically I need a code to look in B8 for text and if there is text I need the code to place a formula in the corresponding "D" column.

so for instance if B8 says adam then i need a code to paste =VLOOKUP(B8,Sheet1!A:B,2,FALSE) and so on for the entire column B and D??