Excel Formula Help ! Bill Of Material Calculation

Dear Experts,
i Would Like To ask if some one can develop a formula for me it calculates the material Requirement plan here is a few description below:
for example:
the Bill Of Material like Below
Mobile D55
Product Code Description Qty
MOB0001 Screen 1
MOB0002 Microphone 1
MOB0003 Key Board 1
Mobile D56
Product Code Description Qty
MOB0001 Screen 1
MOB0002 Microphone 1
MOB0004 Touch Screen 1

The Material Requirement Plan For the MOB0001 (Screen) = Planned production in the specific period * (Mobile D55 Qty+ Mobile D56).

so the formula should look-up for the product code and give the qty and also look-up for the Mobile plan in the production plan and give the quantity so it can be multiplied by it.


if its not clear yet please don't hesitate to ask me

i need this formula so bad it will help me a lot in my calculation

Your utmost reply will be highly appreciated

Kindest Regards,
Wail

Can you post a sample?