Index Match Formula help. Index Match of Health status of an activity into Sheet Summary

I have multiple sheets with the same columns (Health, Task Name, Start, Finish). Note that the Health column is a symbol (red, yellow, green, blue). In my summary sheet, I am using the following Index Match formula to search for Task A in Task Name column and spit out Health symbol.

=INDEX(Health:Health, MATCH("Task A", [Task Name]:[Task Name], 0))

However, on most (not all) of my sheets, I am getting #INVALID OPERATION error and I can't seem to figure out why.

Note: Both Field 38 and Health column is formatted as symbol. (see attached).

Find more posts tagged with

Formulas

Sheet Summary

Project Management

Accepted answers

All comments

Ipshita

Hi @Omid

I tested out your scenario and I am getting the results here (this is my summary sheet) - by using the formula

=INDEX({Task Health}, MATCH("A", {Task Name}))

Hope this helps,

Cheers!

Ipshita

Omid

https://community.smartsheet.com/discussion/comment/364298#Comment_364298

Hi @Ipshita - I'm getting #INVALID REF error. I don't see Task Health a column in your sheet. Also, "A" is not in your sheet. Not sure how you're able to pull in the blue from your sheet into the Summary Sheet.

Ipshita

@Omid

That is because, I had only attached a screenshot of my test summary sheet for you, here is the test master sheet where I have the Project name, task health, task status etc -

and here is the result in the summary sheet -

Cheers,

Ipshita

@Omid

Here is more test results for you -

Summary Sheet

Master Sheet

So the values of INDEX match are derived from the master sheet on to the summary sheet.

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.