#NO MATCH in Index/Match for Grandparent Row

I have a typical hierarchical project plan sheet with the project name on Row 1 and every other row indented under it to various levels.

The Setup:

Column: "TaskCounter". An Auto-number column with no Prefixes or Suffixes.

Column: "TaskID". Column Formula: =MATCH(TaskCounter@row, TaskCounter:TaskCounter, 0)

Column: "Level". Column Formula: =COUNT(ANCESTORS())

Column: "WS Name Row". Column Formula: =MAX(COLLECT(TaskID:TaskID, TaskID:TaskID, @cell < TaskID@row, Level:Level, 3))

Column: "Workstream Name". Column Formula: =IF(Level@row = 3, [Task Name]@row, INDEX([Task Name]:[Task Name], MATCH([WS Name Row]@row, TaskID:TaskID, 0)))

The problem is in the "Workstream Name" column. It displays #NO MATCH for any row with a Level greater than 3. Those are the rows I actually need.

The Problem: Rows 360, 366 and 367 should display "WS7: Hardware" in the "Workstream Name."

There is a clear match on the value of 359, but the Index/Match formula returns NO MATCH. Why?

I realize this is a complex one -- any help is appreciated.

Find more posts tagged with

Accepted answers

Manuel W.

Update: This randomly started working again, just as mysteriously as it stopped working. The formulas are exactly the same as when I posted it and none of them rely on other sheets. The only thing that changed was some rows were deleted and some unrelated cell links created. Maybe some of those rows were... buggy?

All comments

Manuel W.

KPH

Glad you got it sorted. If any of the rows that were deleted had a NO MATCH error in them they would cause a NO MATCH error even if they weren't the thing being matched. Does that make sense? The NO MATCH in the column sort of throws everything off. (that's my technical explanation 😉)

Manuel W.

@KPH, do you mean NO MATCH errors in ANY column? Or one of the columns specifically? It's quite possible that those rows I deleted had errors in some of the other, unrelated columns, but they would have been hidden by IFERROR.

KPH

I've seen NO MATCH anywhere in the columns being used causing an issue but I am afraid that is the limit of my knowledge. It is usually the thing I check for next if someone's syntax looks good and always resolves the issue so I've never probed any further.

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.