Calculating Total Lead Time

[Bearcat Brew]

I have a spreadsheet that has a series of single level bill of materials. Column A is the part number, Column B is the bill of material level, Column C is the parent for the part number, column D is the lead time/processing time for the part (total time to order the part and assemble it into the parent). I am trying to calculate the total lead time for each part using a formula in column E. For an example, a level 3 part would add together its lead time, the lead time for its parent (level 2 part), and the lead time of the parent's parent (level 1 part). Note: we are not considering level 0 since assembling all of the subassemblies to it takes relatively no time.
If the parts were listed in order from parent to lowest level child or child to parent, I have seen solutions here for calculating BOM quantities that could be potentially adapted to sum lead times, but that is not the case here - the parts can be listed in any order. My current solution is not elegant whatsoever and only goes down to a level 5 part. It is a series of progessively more nested VLOOKUP terms that are summed:

=D2+IF(B2>1,VLOOKUP(C2,A$2:D$41,4,FALSE),0)+IF(B2>2,VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>3,VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>4,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>5,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)

If I end up having a level 6 part, I will need to add an even longer term onto the end of this formula and if I end up with a level 7 part at some future point, I think the formula will run too long to fit in a cell. Is there a better way of doing this? I have been racking my brain thinking that SUMPRODUCT can somehow come to the rescue, but have come up empty so far.

 

[wisewood]

Without me having to decode those nested VLOOKUP's - is there a specific location that it's looking for the lead times of these various levels?

Ie. Sheet2 A1:A7 ??

Should be relatively easy to create a VBA script to do this quite efficiently I would have thought.

 

[Bearcat Brew]

Column D has the lead times. The solution needs to be a formula, no VBA this time.

The formula listed in the original post is in Cell E2 It starts with D2 (its own lead time), then sums terms that find the parent, parent's parent, etc., as many levels as needed until it gets to the highest level part.

Book2
	A	B	C	D	E
1	Part#	BOMLevel	Parent	LeadTime	TotalLT
2	15981	5	16481	101	203
3	16478	2	17902	14	28
4	16479	3	16478	14	42
5	16481	4	16479	60	102
6	16911	3	17696	90	92
7	16912	2	17902	7	21
8	16913	2	17902	7	21
9	16931	2	17902	7	21
10	16942	2	17902	7	21
11	17144	1	18111	92	92
12	17418	3	17691	90	105
13	17543	1	18111	1	1
14	17545	2	17543	1	2
15	17621	2	17622	30	31
16	17622	1	18111	1	1
17	17691	2	17695	14	15
18	17695	1	18111	1	1
19	17696	2	17695	1	2
20	17697	2	17695	1	2
21	17838	2	17695	7	8
22	17865	3	17545	30	32
23	17893	1	18111	60	60
24	17896	1	18111	30	30
25	17897	1	18111	30	30
26	17899	1	18111	14	14
27	17902	1	18111	14	14
Sheet1