Index Match Parent Rows Only

mhint2005

I've got a cross sheet reference Index Match formula that is working on some cells, but not others. Here's the issue:

My source sheet has a list of clients (Company column). I have a helper column in that sheet with a formula to check parent rows. I am trying to pull the Status and Type from that sheet into my Metrics Sheet.

In my Metrics Sheet, here are my formulas:

Client Status column: =IFERROR(INDEX({Client Status}, MATCH(Client@row , {Client}, 0)), "")

Client Type column: =IFERROR(INDEX({Client Type}, MATCH(Client@row , {Client}, 0)), "")

The formula is working for some clients but not others. Should I incorporate my helper column into my formula, and if so, how?

Thank you!

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @mhint2005

First, check the reference.

You can check the reference by the Sheet reference manager, which you can access by right-clicking anywhere in the sheet. Look for the menu at the lower part of the menu panel.

Then, check the 'Edit Reference."

Ensure your reference includes the entire column.

As for the formula, yours appears to be fine.

Mine uses the COLLECT function, as I wanted to show you several ways to reference the source sheet.

https://app.smartsheet.com/b/publish?EQBCT=93a4f1216f7a4304a8b6dc29296561fa

The first one is the parent-only formula.

=IFERROR(INDEX(COLLECT({Source Sheet : Status}, {Source Sheet : Company}, Client@row , {Source Sheet : Helper Column}, true), 1), "")

The second is the first child.

=IFERROR(INDEX(COLLECT({Source Sheet : Status}, {Source Sheet : Company}, Client@row , {Source Sheet : Helper Column}, false), 1), "")

The third is the last child.

=IFERROR(INDEX(COLLECT({Source Sheet : Status}, {Source Sheet : Company}, Client@row , {Source Sheet : Helper Column}, false), COUNTIFS({Source Sheet : Company}, Client@row , {Source Sheet : Helper Column}, false)), "")

The last is to show all that matches the client or company.

=JOIN(COLLECT({Source Sheet : Status}, {Source Sheet : Company}, Client@row ), CHAR(10))

https://app.smartsheet.com/b/publish?EQBCT=e2c7be1837964d59aaa8c1f75725f0e3

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.