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?

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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)

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)

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),...

Paul Newcome

My apologies. I misread your original formula.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.