Why is my INDEX MATCH formula breaking after 2 rows?

olsxn

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?

Thanks

Paul Newcome

With INDEX/MATCH, you only want to pull in individual columns. So change your first reference to only reference the date column and the second reference to only reference the unique id column.

=INDEX({Sheet2 Date Column}, MATCH([UniqueIdentifier]@row, {Sheet2 ID Column}, 0))

Paul Newcome

With INDEX/MATCH, you only want to pull in individual columns. So change your first reference to only reference the date column and the second reference to only reference the unique id column.

=INDEX({Sheet2 Date Column}, MATCH([UniqueIdentifier]@row, {Sheet2 ID Column}, 0))

olsxn

@Paul Newcome thank you very much! perfect fix

Paul Newcome

Happy to help. 👍️