This is the type of problem that bumps up against the limitations of Formulas. A couple things formulas aren’t currently designed for (1) caching information (2) accessing data from other rows.
(1) In your case, caching is trouble for the Year value since functions like TODAY() don’t cache and will continue to update with time. Manually set Date or Single Select fields are the norm instead.
(2) A formula runs once per row and doesn’t have access to other rows’ data (unless Linked Records are being used). So awareness of some repeating, row based pattern is difficult.
Solutions to fill the void are the same in both cases:
- a formula that concatenates a few fields together automatically, then have users employ clever copy/pasting and spreadsheet work on import
- integrations and the API for heavier weight, third party solutions, which can also employ auto-filling beahvior