Help with Index Match bringing back Children row data based on Parent Row Match

How do I get Index/Match to do the following:

Look at Source sheet column for a Match in a Parent row and if there is a match, pull in two date columns for all the CHILDREN.

This formula is pulling in dates but only for the first PARENT/CHILD section. It's not checking the MATCH on the Parent Asset #.

=INDEX(COLLECT({Test AMT Task Started}, {Test Parent}, PARENT([Deliverables and Tasks]@row), {Test AMT Asset/Tasks}, [Deliverables and Tasks]@row), 1)

This formula say's Unparseable.

=(INDEX(COLLECT({Test AMT Task Started}, {Test Parent}, PARENT([Deliverables and Tasks]@row), {Test AMT Asset/Tasks}, [Deliverables and Tasks]@row), MATCH(PARENT({Test AMT Asset #}, {Asset #}, 1))))

Thanks!!

Find more posts tagged with

Accepted answers

Paul Newcome

I suggest a helper column where you replicate the parent row data on each of the child rows. Then you should be able to use an INDEX/COLLECT and include this helper row as a range/criteria set.

All comments

Andrée Starå

Hi Jeana,

Unfortunately, it doesn't work with parents.

The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS.

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Jeana

Bummer! Thanks for the confirmation. Now to ponder a work around. :-)

Jeana

So if I eliminate the PARENT/CHILD relationship my sheet looks like this:

My goal is to pull in the ACTUAL START DATE and ACTUAL END DATE into this sheet based on the Asset # and Deliverables and Tasks (this is what is unique).

I've tried several variations of INDEX COLLECT but can't seem to get it right.

=INDEX(COLLECT({Test AMT Task Started}, MATCH(COLLECT([Asset #]@row, [Deliverables and Tasks]@row), {Asset #}, {Test AMT Asset/Tasks}, 0)))

Screen Shot 2020-06-06 at 7.57.35 PM.png

Andrée Starå

@Jeana

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Andrée Starå

@lewis hamilton

Your solution is for Excel and not Smartsheet so that it won't work, unfortunately.

Jeana

@Andrée Starå thanks for the offer. I don't have this exact issue anymore but a similar one. I have two sheets that I can use Index/Match on to return data on that row (where the match occurs). I'd like to be able to pull data from the CHILDREN of the row that Index/Match returns. Here's a screen shot that should help.

If INDEX/MATCH is successful for Title for this Template, can I return data in Step 1 or other Steps?

Screen Shot 2020-08-13 at 9.47.18 AM.png

Thanks for your time!!!

Jeana

Paul Newcome

I suggest a helper column where you replicate the parent row data on each of the child rows. Then you should be able to use an INDEX/COLLECT and include this helper row as a range/criteria set.

Andrée Starå

@Jeana

Excellent!

You're more than welcome!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

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.