Why am I receiving #INVALID OPERATION error in some of my sheets

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.

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

However, on some of my sheets, I am getting #INVALID OPERATION error and I can't seem to figure out why.

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

All comments

Paul McGuinness

Hi @Omid

Are all the columns formatted correctly as symbol and the same type?

Where you are receiving invalid operation errors is the criteria Task A present in those sheets? as the error could be that it cant find Task A so cant deliver a result.

Thanks

Paul

Omid

https://community.smartsheet.com/discussion/comment/361105#Comment_361105

Hi @Paul McGuinness , The Health column is formatted as symbol. (see attached) and Task A is within the sheet under Task Name column.

Capture.PNG

Omid

https://community.smartsheet.com/discussion/comment/361105#Comment_361105

Hi @Paul McGuinness , The Health column is formatted as symbol. (see attached) and Task A is present within the sheet under Task Name column. The field in summary sheet is also formatted as Symbol.

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.