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!