Setting a range using a hierarchy

ib

Hi, I've got an INDEX function that looks up values in a group of rows under a parent in the same sheet.

=INDEX(ID$3:$Goal$183, MATCH(ID214, $ID$3:$ID$183, 0), 5)

Instead of having $183 I'd like to use a function to set the range to everything under the parent (in row 3), no matter how deep it is. Is there a way to do this?

Paul Newcome

Try...

CHILDREN(ID$3)

for your range.

ib

Right but that only gets the first level children (e.g. in the case count returns 5), I would need to iterate through all children so i get the full range. Unless I'm missing what Children() returns since

=INDEX(ID$3:CHILDREN(ID$3), MATCH(ID205, $ID$3:$ID$176, 0), 5) = "Unparseable"

I initially tried something like , but i would need an "offspring" or something similar to the Ancestor() function to get all children.

=INDEX(ID$3:"$Goal"+COUNT(CHILDREN(ID$3)), MATCH(ID205, $ID$3:$ID$176, 0), 5)

Paul Newcome

Sorry about that. I didn't realize you had multiple levels of hierarchy. Try a DESCENDANTS function like so...

=INDEX(DESCENDANTS(ID$3), MATCH(ID214, DESCENDANTS($ID$3), 0), 5)

ib

So i've tried that, and i still get "INVALID VALUE", does Descendants return both a row and column range or just the row range?

ib

I tried doing a concatenation but no joy: INDEX(ID$3:"Goal"+(COUNT(DESCENDANTS(ID$3),...

Paul Newcome

My apologies. I misread your original formula.

=INDEX(DESCENDANTS($Goal$3), MATCH(ID214, DESCENDANTS($ID$3), 0))