Index and Match Gives Random Results


I am building an audit sheet to show where end dates and status do not correlate correctly. I've built and index and match for the End Date column that functions correctly, but cannot get the Status to Index and Match consistently. Source sheet sample below.

The Index and Match seems to work sporadically for the Status Column. My full formula is below. And an image of my audit sheet with the issue is below that. I cannot figure out why the Status reference is not consistent. All Task Name entries are unique. Column3 below is only the index and match formula for testing.

=IF(AND(INDEX({ReferenceEndDate}, MATCH([Task Name]@row, {ReferenceTaskName}, 0)) < TODAY(), INDEX({ReferenceStatus}, MATCH([Task Name]@row, {ReferenceTaskName})) <> "Completed"), "Red", "Green")


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!