Function for searching the upper parent in a company structure.

Hi all,

First of all, thank you for reading this. And hopefully one of you can help me out.

I've have an export of an company structure and would like to know an formula which can find the direct entity parent in the company structure. The layout of the export is as follows;
Entity 1 is the mother, all other entities have different locations within the company structure. You can find the upper parent by moving one cell to the left in which the entity is located and move up to the first non blank cell/entity, see attachment.




I'm looking for an formula which can retrieve the direct upper parent. I would like to plug the formula in column J behind each entity, this would be beneficial for future purposes. I would like the formula to register the column the entity is in and find the upper parent by moving 1 cell to the left and retrieve the first non blank cell above. So for example Entity 7; That the formula registers the entity is in cell F7, moves to cell E7 and goes up to the first non black cell which is E6 and returns Entity 6. For Entity 13; That the formula registers the entity is in cell B13, moves to cell A13 and goes up to the first non black cell which is A1 in this case will return Entity 1.

Maybe the above way of solving the issue is very redundant and feel free to suggest an more simple solution.

Already my big thanks for anyone willing to help me out.

Gr Koetel

Met udf

Please Login or Register  to view this content.

Please Login or Register  to view this content.

HI Jec,

It works perfectly, thank you so much!

Have a nice weekend