Index and Match Gives Random Results

Elliot Holder

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

Paul Newcome

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

Paul Newcome

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