INDEX/MATCH not working

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Below are two screenshots. The first is a screenshot of my target sheet. The other is a screenshot of my reference sheet.

 

On the reference sheet I have a list along the left. In the column to the right is:

=TODAY()

 

On the target sheet is an identical list with the following formula to the right:

=IF(TODAY() = INDEX({New Sheet Test Range 1}, MATCH([Primary Column]@row, {New Sheet Test Range 1}, 0), 2), "Matched Up", "Not Matching")

 

What this SHOULD do is look on the reference sheet for the value that is in the @row position on my target sheet. If it finds that value and the value in the column to the right of that equals TODAY(), then it would say Matched Up. If the date did not equal TODAY(), it would say Not Matching. 

 

Theoretically... They should ALL say "Matched Up" on my target sheet as the reference list is exactly identical and the dates on the reference sheet are populated by =TODAY(). Theoretically...

 

What is actually happening is that half way down my list it suddenly finds that the dates don't match, and in the last row, I have an #INVALID DATA error.

 

I have copy/pasted, manually typed, and drag filled hoping that maybe it was a typo I had made somewhere or something, but I always get the same result. I have also tried different date variations making sure they matched up hoping to force a match to no avail. It is always in the same positions that it changes too. Halfway down the list it turns to mo match and then the last row is #INVALID VALUE. It doesn't matter if its 26 rows or 100 rows. I've even tried creating rand new sheets. Nothing is working.

 

Any suggestions?

Capture.PNG

Capture2.PNG

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!