# Look Up BOM Hierarchy using Level field

#### Prozen

##### New Member
Hi all,

I am trying to come up with a method of looking up the BOM structure below, using the level column, to replicate the manual output i have created in the column 'IDEAL'.

The logic here is
1) If 'ISCONF' = 'N' then it remains 'N'
2) If 'IsConf' is 'Y' then look up at its parent, which would be its 'Level' - 1 to see if 'IsConf' is also 'Y'. This needs to cycle all the way up to its related Level 2. If its parent/grandparent/great-grandparent etc, all the way up Level 2 has IsConf = 'N' then the output for that row should be 'N'

 Level Fitting Funct Part IsConf IDEAL 0​ SUB123 Y Y 1​ RCI SUB234 Y Y 2​ RIA OLD123 N N 3​ Group1 OLD234 Y N 4​ 1_1_1 OLD345 Y N 4​ 1_1_2 OLD456 Y N 2​ RIA NEW345 Y Y 3​ Group1 NEW234 Y Y 4​ 1_1_1 NEW345 Y Y 4​ 1_1_3 NEW456 N N 4​ 1_1_3 MAS456 Y Y

Previously I've used something like the below to look up what the 'IsConf' is for its parent but i have no idea how expand on this to cycle up multiple levels to get the result without creating numerous columns to repeat the same formula

=IF(A2=0,"Y",LOOKUP(2,1/(\$A\$2:\$A2=A2-1),\$E\$2:\$E2))

Any assistance is much appreciated!

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### jasonb75

##### Well-known Member
Your post is more than a bit confusing this part,
This needs to cycle all the way up to its related Level 2.
implies that the formula doesn't need to look above level 2, if that is the case then why are some level 3 and level 4 rows N when IsConf is Y?

How do you identify which level 2 item is related to which level 1 item, etc? There doesn't appear to be any consistency in your example.

#### Prozen

##### New Member
So in the snippet below IsConf = Y for Level 3 and Level 4 rows, however when you work your way up from either of these level's when you get to Level 2 'IsConf' = N. This then makes them 'N' rather than a 'Y'.

 Level Fitting Funct Part IsConf IDEAL 0​ SUB123 Y Y 1​ RCI SUB234 Y Y 2​ RIA OLD123 N 3​ Group1 OLD234 Y N 4​ 1_1_1 OLD345 Y N 4​ 1_1_2 OLD456 Y N

When looking at the relationships between the Parts its probably better visualized with the indents below as
 Level Fitting Funct Part IsConf IDEAL 0 SUB123 Y Y 1 RCI SUB234 Y Y 2 RIA OLD123 N N 3 Group1 OLD234 Y N 4 1_1_1 OLD345 Y N 4 1_1_2 OLD456 Y N 2 RIA NEW345 Y Y 3 Group1 NEW234 Y Y 4 1_1_1 NEW345 Y Y 4 1_1_3 NEW456 N N 4 1_1_3 MAS456 Y Y

#### Prozen

##### New Member
I have managed to get there in a slightly long winded way by
1. In column D i converted the 'Y' to 1 and 'N' to 0
2. In column E i use =D2*IF(A2=0, 1, LOOKUP(2, 1/(A\$1:A2=A2-1), E\$1:E1)) to apply the logic in the first post (took this from Google where someone was calculating quantities of parts)
3. Column F to convert it back from 1 and 0 to 'Y' and 'N'

Column G was just to show it matched the output i was looking for. If there are any thoughts on how i can clean this processes up and save not having to create 3 columns would be greatly appreciated.

Sample.xlsm
ABCDEFG
1LevelItemIsConfIsConf (as number)FormulaConvertedIDEAL
20GarageY11YY
31CarY11YY
42FrontN00NN
53TyreY10NN
63RimY10NN
74NutY10NN
82RearY11YY
93Wheel2Y11YY
104NutY11YY
114BoltN00NN
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=D2*IF(A2=0, 1, LOOKUP(2, 1/(A\$1:A2=A2-1), E\$1:E1))
F2:F12F2=IF(E2=1,"Y","N")

#### jasonb75

##### Well-known Member
I couldn't see what was needed from your first reply because the indents are not showing, however I can see it from the 'long winded way' that you posted.

I have it down to a single column but row 7 is coming up with Y instead of N, hopefully it won't take me too long to figure out why.

edit:- this looks good
Book2
ABCDE
1LevelItemIsConfFormulaIDEAL
20GarageYYY
31CarYYY
42FrontNNN
53TyreYNN
63RimYNN
74NutYNN
82RearYYY
93Wheel2YYY
104NutYYY
114BoltNNN
Sheet19
Cell Formulas
RangeFormula
D2:D12D2=IF(A2=0,"Y",IFERROR(LOOKUP(2,1/(A\$1:A2=A2-1)/(C2="Y"), D\$1:D1),"N"))

Last edited:

#### Prozen

##### New Member
That works perfectly, Jasonb75! Thank you very much

Replies
6
Views
2K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,480
Messages
5,770,341
Members
425,612
Latest member
martinijr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back