Why is my INDEX MATCH formula breaking after 2 rows?

edited 02/19/24 in Formulas and Functions

Hi, any help with this will be hugely appreciated :)

I have two sheets, both have 2 identical columns, a unique identifier and a date column. There are 5400 rows on these sheets so I was hesitant to copy and paste as I will not be able to check each one individually and of course would prefer this automatic setup for future additions.

For Sheet 1's date column, I wrote the formula below:

=INDEX({Sheet2 range 1}, MATCH([UniqueIdentifier]@row, {Sheet2 range 1}, 0), 2)

where Sheet 2 range 1 is both the unique identifier and date columns as shown below.

This works perfectly well for the first 2 rows but on the 3rd it says 13/02/25 instead of 02/12/25. This is followed by a lot of mistakes and then 2700 rows down there are 5 rows of no date at all, then followed by #INVALID VALUE for the next 2700 or so rows.

I suspected this might be a problem with the date format on my account and on the sheets, I changed these so they are all matching and in UK format (dd/mm/yyyy) but it didn't change the values.

Is my formula wrong somewhere?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!