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

Omid

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).

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

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.