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).
Answers
-
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
Ipshita Mukherjee
-
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.
-
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
Ipshita Mukherjee
-
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.
Ipshita Mukherjee
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 215 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!