Trying to search and return text found in Ancestors

sbrittin2

I am trying to search the ancestors for any given cell and return the content of the ancestor if it meets specific criteria.

For example, if the Ancestor has the word "Chapter," I would like it to return all of the content from that Ancestor cell.

I've started with this formula, and I know the PARENT part is what's wrong. Unfortunately, I don't know what to use instead. (I don't know how to pull an Ancestor like you can a Parent.)

=IF(CONTAINS("Chapter", ANCESTORS([Task Name]@row)), PARENT([Task Name]@row))

Any help would be much appreciated!!

Brian_Richardson

Ok not sure why CONTAINS won't function in a MATCH, but instead of doing INDEX MATCH you can simply COLLECT the results that you're looking for, JOIN them, and place in a cell with this formula:

=JOIN(COLLECT(ANCESTORS([Task Name]@row), ANCESTORS([Task Name]@row), CONTAINS("Chapter", @cell)), ",")

• COLLECT puts together an array of Ancestor values where the Task Name contains the word "Chapter"
• Then JOIN takes the results and posts them into your cell, separated by a ,

Brian_Richardson

= INDEX(ANCESTORS([Task Name]@row), MATCH(CONTAINS("Chapter",@cell), ANCESTORS([Task name]@row), 0))

This works like this, from "inside" out:

• ANCESTORS gives you a temporary list of the values of all the ancestors of your row
• CONTAINS looks through that list of Ancestor values one at a time (the @cell part) to find one with "Chapter"
• MATCH returns the position in the list of Ancestors where "Chapter" was found
• INDEX returns the value from that list of Ancestor values, from the position that MATCH returned

Brian_Richardson

Hmm…strike that. I typed this out before trying it. I'm getting INVALID OPERATION due to the CONTAINS and I'm not sure why.

Brian_Richardson

Ok not sure why CONTAINS won't function in a MATCH, but instead of doing INDEX MATCH you can simply COLLECT the results that you're looking for, JOIN them, and place in a cell with this formula:

=JOIN(COLLECT(ANCESTORS([Task Name]@row), ANCESTORS([Task Name]@row), CONTAINS("Chapter", @cell)), ",")

• COLLECT puts together an array of Ancestor values where the Task Name contains the word "Chapter"
• Then JOIN takes the results and posts them into your cell, separated by a ,

sbrittin2

It looks like that did the trick! Thank you!