If Statement Inside A Vlookup

I have a list of parts that I have to create in a system. It is easier to do if I create a list of materials ahead of time of what components are needed. What I have done is create a part list that contains a list of parts, what the cost, whether they are manufactured or purchased, and what the sub component is if they are manufactured.

This worked great till I ran into my first sub component that contain its own sub components. I am attaching the spreadsheet so everyone can see what I am doing or more to the point trying to do. IF VBA is a better solution that would be great.

What I need is a formula (or VBA if it is better) that brings back the sub-components and their sub-components for each part until the parts tree for the original part number reaches the base purchased sub-parts.

I have the 1st worksheet set up in three different ways. The first one you run into is simply a length calculator and can be ignored.

The second way is the original way I had the spreadsheet working and it works fine for parts with only one sub-component. If a sub-part has more than one sub-component then it doesn't work.

The third section is where I started building what I thought would work, but it is clumsy and only will work nicely if I can place an IF statement that will only bring back a value for a sub-component if one exists otherwise it will remain blank.

What I would like is to have a formula that will auto fill the information IF and only if it exists for each sub-part listing all sub-components ( I need if it exists, cost, whether it is manufactured or purchased, any and all sub-components [followed by the same information as the parent] and vendor).