Hello jonnykats, welcome to the board.
A couple things . . .
(1) Please turn off your CAPS LOCK, eh? It makes your message more difficult to read and makes it look like you're shouting at us.
(2) Here's an example of what SteveO59L is suggesting.
On your Materials List sheet, highlight the entire range of material names, costs, retails & profits and name this range. (Insert > Name > Define . . . and name it
MaterialsData
Next, highlight just the matarials names (column A?) of that same range and name that range
MaterialsNames
Now, in your Input sheet in B1 along side your dropdown menu, enter this formula:
=IF($A1="","",VLOOKUP($A1,MaterialsData,2,0))
In C1, enter this one:
=IF($A1="","",VLOOKUP($A1,MaterialsData,3,0))
and in C1 enter this one:
=IF($A1="","",VLOOKUP($A1,MaterialsData,4,0))
Copy these down columns B:D to the bottom of your list.
Now, when you select one of the material names in column A, the cost, retail & profit data should show up in the columns to the right.