Populating the part numbers based on Level numbers in multi level BoM

Hello

Based on the column A `Level number' part numbers to be plotted as shown in the light yellow columns D:E as results by formulas.

Note :
* Level `0' part numbers will usually more than one and different part numbers, but here only one `0' part number available.
* In Light yellow cells results need to be produced for all cells but shown only for some cells for illustration purpose which is also intermittent.

Thanks in advance

Sekar

Maximum number of levels is ?

And are you still on Excel 2010 ? if not, please update your profile as later version(s) of Excel may have functions useful in a formula solution.

Maximum number of level is upto 18 depending upon the cases. In the attached excel it is 9. I can use the Excel 2016 version.

Then please update your profile re Excel 2016.

I can use the Excel 2016 version.

Update your profile, please.

Please try at
D3

=IF(A3=1,$B$2,LOOKUP(2,1/(A$1:A2=A3-1),B$1:B2&" > "&D$1:D2))

E3
=IF(A3=1,$B$2,LOOKUP(2,1/(A$1:A2=A3-1),E$1:E2&" > "&B$1:B2))

I think the result in row 18 is incorrect: it adds in the last of the level 2 in rows 15-17 for the Level 3 and (I assume) that 15-16 should be accounted for in multiple level 3 results i.e. one for each level 2.

????

If the above is correct, then there there are multiple occurrences of this situation.

This formulas worked well and saving my huge time, Thank You. I have changed to Excel 2016 version in this forum. Also row 18 is correct.

so B15 and B16 are AAMA-OXXOF/AAMA-XPCAA and B17 AKGA-PGPGX , but C18 is AKGA-PGPGX > AMPA-MPPOG > AOKA-AFKPC

what happened to AAMA-OXXOF and AAMA-XPCAA?