Help requested in creating an array formula

Dear Experts,

First my sincere greetings to all.

I have two worksheets, (Please find the same in the attachment) namely "Master" and "April". Sheet "Master" is containing details of cycle time for a particular part number for various machines. Sheet "April" contains the qty produced in different part number in different machines in April month.

My objective from this is to calculate the total cycle time each machine is booked for. I have identified the machine names in sheet "April" in cells G1:CL2.

From the table, a formula is required, which will

1. Take partnumber from column A of sheet "April" as 1st reference, and Machine name from Column B of sheet "April" as 2nd reference, and

2. Find the cycle time specified for the corresponding part number from sheet "Master", and

3. Multiply the cycle time in column C of sheet "Master" with produced qty in column D of sheet "April".

4. Result should be displayed against respective machies from column G2:CL1111 for different part numbers and different machines.


I tried with product & if combination which proved I am not correct. Can anybody please help me to complete the same?


My sincere thanks to all.

Thanks and regards,

gm2612

Trial Worksheet.xls