Proceeding sibling

[Nic Walmsley]

Hi there

I'm looking for some help with an excel formula. Have tried myself but is beyond my capabilities.

In the table below, I need a formula for Column D. If I were to characterize the rule for this column, it would be:

Look up at all the Node values above the current row, and find the next node with the same level as the current row, BUT if you encounter a node with a lower level number first, leave the cell blank.

So for example, if the row I'm on is for a node at level 3, it would look up and find the next node from level 3 (looking upwards from my row towards the top). But if it hit a node from a lower level (2 or 1 in this example) before it found another level 3, it would leave Column D blank.

D could be characterized as "proceeding sibling"

Here's an example of how it should look:

Node	Level	Parent	Proceeding Sibling
Food	1
Friut	2	Food
Apple	3	Fruit
Orange	3	Fruit	Apple
Banana	3	Fruit	Orange
Grape	3	Fruit	Banana
Red Grape	4	Grape
Green Grape	4	Grape	Red Grape
Meat	2	Food	Fruit
Beef	3	Meat
Chicken	3	Meat	Beef
Lamb	3	Meat	Chicken
Fish	3	Meat	Lamb
Snapper	4	Fish
Cod	4	Fish	Snapper
Desert	2	Food	Meat
Veges	2	Food	Desert
Potato	3	Veges
Carrot	3	Veges	Potato

[#2]

I think i can do it, but only with a couple of helper columns. I've attached a workbook that does what you wanted..

The helper cells do the following.

Row Number: an array formula that returns the largest row number that the current node apears in above the current row

If statement: uses the row number to check that the smallest number between whatever row we found above and the row 1 above the row we are on now isn't smaller than the current node

I cant combine them in to one huge, probably impossible to edit formula as the if statement brakes if its entered as an array.

Hopefully that helps.

[#3]

It looks like duplicated post

https://techcommunity.microsoft.com/t5/Excel/Help-with-tricky-formulas/m-p/224608#M16794

[#4]

thanks!