Why is my INDEX MATCH formula breaking after 2 rows?

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
Best Answer
-
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))
Answers
-
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 thank you very much! perfect fix
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!