Returning Cell Value to Different Cell Based on Criteria

[Cat129]

I'm not sure how to explain what I need so hopefully the cells below explain. I have created some dummy data similar to what I need.

I'm trying to create a formula to go into the parent column (G) that will return the value from the part number column (B) of the parent above. All parents are a level 5. I have completed Column G to show you what I would like the end result to be

Any help would be really appreciated as I have been trying with index match using the level (Column D) as a reference and nothing I try works.

The solution can be in VB or a regular formula as this is an automated sheet which will all be coded eventually.

Thank You
Cat

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Part Number[/TD]
[TD]Revision[/TD]
[TD]Level[/TD]
[TD]Qty[/TD]
[TD]Description[/TD]
[TD]Parent[/TD]
[/TR]
[TR]
[TD]Parent 1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]Assembly[/TD]
[TD]Parent 1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]Part of Above[/TD]
[TD]Parent 1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]Assembly[/TD]
[TD]Parent 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]Part of Above[/TD]
[TD]Parent 1[/TD]
[/TR]
[TR]
[TD]Parent 2[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]Assembly[/TD]
[TD]Parent 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]Part of Above[/TD]
[TD]Parent 2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]Part of Above[/TD]
[TD]Parent 2[/TD]
[/TR]
[TR]
[TD]Parent 3[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]Assembly[/TD]
[TD]Parent 3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]Part of Above[/TD]
[TD]Parent 3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]Part of Above[/TD]
[TD]Parent 3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Put this formula in cell G2 and copy down...

=IF(D2=5,B2,G1)

 

[Cat129]

Put this formula in cell G2 and copy down...

=IF(D2=5,B2,G1)

Hey Rick, Thanks for the response, unfortunately I dont think this will work. What happens when I get to D6 or D9? The formula would then be providing the wrong parent.

In the actual data parents have between 1 and over 300 child components within them, the amount of children also changes with each update so the formula would need to be dynamic

 

[Cat129]

Hey, Sorry. Was being thick. Yes this does work. I was trying to over complicate things.

Thank You for your help