#### Bearcat Brew

##### New Member

- Joined
- Dec 7, 2004

- Messages
- 40

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.