Setting a range using a hierarchy
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?
Answers
-
Try...
CHILDREN(ID$3)
for your range.
-
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)
-
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)
-
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?
-
I tried doing a concatenation but no joy: INDEX(ID$3:"Goal"+(COUNT(DESCENDANTS(ID$3),...
-
My apologies. I misread your original formula.
=INDEX(DESCENDANTS($Goal$3), MATCH(ID214, DESCENDANTS($ID$3), 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!