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
-
Try adding the zero into the last portion of your second MATCH function.
=IF(AND(INDEX({ReferenceEndDate}, MATCH([Task Name]@row, {ReferenceTaskName}, 0)) < TODAY(), INDEX({ReferenceStatus}, MATCH([Task Name]@row, {ReferenceTaskName}, 0)) <> "Completed"), "Red", "Green")
Answers
-
Try adding the zero into the last portion of your second MATCH function.
=IF(AND(INDEX({ReferenceEndDate}, MATCH([Task Name]@row, {ReferenceTaskName}, 0)) < TODAY(), INDEX({ReferenceStatus}, MATCH([Task Name]@row, {ReferenceTaskName}, 0)) <> "Completed"), "Red", "Green")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!