INDEX MATCH Formula with row Hierarchy
Hello,
I need to reference information from the Digital column and Match it into the Digital Row. The row in white is the Parent column and the rows in grey are the Children. Here is the formula I'm using:
=(INDEX(PARENT(Digital@row), MATCH("Digital", CHILDREN(WO@row), 0)))
In the Parent row the cell is blank, but in the Children row it says #NO MATCH. I know I have to be close I'm just not sure what to try next.
Thank you!
Best Answers

What exactly are you wanting to accomplish? Are you trying to pull "Kadin" into the cell containing #NO MATCH?

That would look more like this:
=IF(COUNT(CHILDREN()) = 0, IF(WO@row = "Digital", PARENT(Digital@row), IF(WO@row = "Graphics", PARENT(Graphics@row), IF(WO@row = "Fab", PARENT(Fab@row)))), IF(WO@row = "Digital", Digital@row, IF(WO@row = "Graphics", Graphics@row, IF(WO@row = "Fab", Fab@row))))

With nesting, the only limit you should run into would be the limit of 4,000 characters per cell (including spaces).
Answers

What exactly are you wanting to accomplish? Are you trying to pull "Kadin" into the cell containing #NO MATCH?

Yes! Under the cell in the formula column and Digital row.

If you have the rows indented under row 1 then you should be able to just use
=PARENT(Digital@row)

Oh maybe I should clarify. I need this to pull for every corresponding child row for each order. so whatever value is in the parent row is reflected in the child row in the formula column.
The intention is to pull this information for a report so we can schedule per individual in the Gantt view. The report doesn't pull both the parent and child rows unless the information matches.

Ok. Try this as a column formula:
=IF(COUNT(CHILDREN()) = 0, PARENT(Digital@row), Digital@row)

Great, so that worked. Would I be able to have it be nested so I could pull information for each corresponding child row? So the formula would search the Digital@row, Graphics@row, Fab@row, etc. for values to pull and match to the corresponding child row.

That would look more like this:
=IF(COUNT(CHILDREN()) = 0, IF(WO@row = "Digital", PARENT(Digital@row), IF(WO@row = "Graphics", PARENT(Graphics@row), IF(WO@row = "Fab", PARENT(Fab@row)))), IF(WO@row = "Digital", Digital@row, IF(WO@row = "Graphics", Graphics@row, IF(WO@row = "Fab", Fab@row))))

Hey Paul, this worked! Do you know if there are any limits?

There could potentially be a number of limits. What exactly are you concerned about?

I was just wondering if it would have nesting limits. So far it seems to be working. Thank you for all of your help!

With nesting, the only limit you should run into would be the limit of 4,000 characters per cell (including spaces).
Help Article Resources
Categories
Check out the Formula Handbook template!