Request assistance shortening formula with criteria and different tables

[mrblister]

	A	B	C	D	E	F	G	H
1	MATERIALS MADE BY EACH FACTORY
2	Factory Type	Output Type (used by FACTORY DETAILS table)	Output amount (used by FACTORY DETAILS table)
3	NutFactory	Nut	10
4	FactoryThatMakesNutFactory	NutFactory	3
5	ScrewFactorySmall	Screw	6
6	ScrewFactoryLarge	Screw	11
7
8
9	FACTORY DETAILS
10	Factory	NutFactory	FactoryThatMakesNutFactory	FactoryThatMakesNutFactory	ScrewFactorySmall	ScrewFactoryLarge
11	OutputType	Nut	NutFactory	Nut	Screw	Screw
12	FactoryID	NutFactoryOutputNut	FactoryThatMakesNutFactoryOutputNutFactory	FactoryThatMakesNutFactoryOutputNut	ScrewFactorySmallOutputScrew	ScrewFactoryLargeOutputScrew
13	OvertimeActivationInHours	11	3	9	8	1
14	RegularOutput	2	4	12	11	3
15	OvertimeOutput	4	13	10	5	2
16
17
18	MATERIALS OUTPUT
19		Hours Worked (that day)	Nut	Screw	NutFactory	FactoryThatMakesNutFactory	ScrewFactorySmall	ScrewFactoryLarge
20	Day 0	0	0	0	0	1	1	1
21	Day 1	10	400	210	390	1	1	1
22	Day 2	5	750	515	585	1	1	1
23	Day 3	2	890	637	625	1	1	1

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>

OK, so I have 3 tables on 3 different sheets in a workbook. I've simplified the problem above. I need to shorten the formulas in C21:H23.
The formula is C21 is:
C20+(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,1),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,2),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,3),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 < (INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))),INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)*LOOKUP(INDEX($A$3:$A$6,4),$E$19:$H$19,$E20:$H20),0))

There's a large repeating section (differences in bold), that I think can be replaced by something more space-efficient.

What the formula does:

1. For every Factory Type, first check to see if Hours are high enough for Overtime Output.
2. If yes, there's a specific overtime formula that needs to be run to calculate the Output for that Factory.
3. If Hours are low enough (to not activate Overtime), then RegularOutput is used with a specific regular formula to calculate output.
4. Then multiply that output by the number of that specific Factory.
5. Finally, all the outputs from all Factories are summed together (and the amount from the previous day is added).

NOTES:

• The tables I am using are far larger than these, which makes it necessary to shorten the formulas.
• The formulas to calculate output depending if OvertimeActivationInHours is activated, are very different. I used simple placeholders above.
• The formula is complicated by the fact that there are factories that make factories. So for the example of "FactoryThatMakesNutFactory" we need to take into account the Nut Factories it makes (FactoryThatMakesNutFactoryOutputNutFactory), PLUS the Nuts generated by those new Nut Factories (FactoryThatMakesNutFactoryOutputNut).
• The values in "MATERIALS MADE BY EACH FACTORY" are used by "FACTORY DETAILS" table, which is why some of those values don't appear in the formula.

Any suggestions on how to condense this formula?

 

[mrblister]

OOPS, I just noticed that "MATERIALS MADE BY EACH FACTORY" table isn't relevant to the formula at all! I could just read in the factory names from the second table. I can't remove the first table now though, I'm unable to edit my original post to due the editing rules.

 

[FDibbins]

That sure is a monster!!

In essence...

=(IFERROR(IF($B21 <
INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0)),
INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,
INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,1)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)
*LOOKUP(INDEX($A$3:$A$6,1),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 <
INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0)),
INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,
INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,2)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)
*LOOKUP(INDEX($A$3:$A$6,2),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 <
INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0)),
INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,
INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,3)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)
*LOOKUP(INDEX($A$3:$A$6,3),$E$19:$H$19,$E20:$H20),0))+
(IFERROR(IF($B21 <
INDEX($A$10:$F$15,MATCH("OvertimeActivationInHours",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0)),
INDEX($A$10:$F$15,MATCH("RegularOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))*$B21*5,
INDEX($A$10:$F$15,MATCH("OvertimeOutput",$A$10:$A$15,0),MATCH(INDEX($A$3:$A$6,4)&"Output"&C$19,$A$12:$F$12,0))*$B21*3)
*LOOKUP(INDEX($A$3:$A$6,4),$E$19:$H$19,$E20:$H20),0))

hard to break it down, but I would be leaning towards breaking each part out into a helper column *(1 per part), then summing the helpers.
This has the advantage of making adjustments/trouble-shooting simpler, and you can see what each part is doing - which may make it easier to spot errors?