I need to extract information from 2 different sheets based on the name of the member organisation and the join year (i.e. DD/MM/YYYY). The 2 sheets have the same column titles, but one is for 2024 and another one for 2025. Where organisation joined earlier than 2025, then it needs to fetch the data from the 2024 sheet, and where the organisation joined later (i.e. if previous condition doesn't apply), it needs to fetch it from the 2025 sheet. I am having issues with the formula as I get "invalid data type". Yet I have ensured that the date column (i.e. DD/MM/YYYY) is a date, as is the column this information is being pulled from (another reference sheet). Any guesses what I am doing wrong and how to fix?
See formula and screenshot attached:
=IF(YEAR([Join year]@row ) < YEAR(2025), JOIN(COLLECT({Membership Survey 2024 - Experience}, {Membership Prioritisation Survey 2024 -Org}, [Organisation name]@row ), JOIN(COLLECT({Membership Survey 2025 - Experience}, {Membership Prioritisation Survey 2025 - Org}, [Organisation name]@row ))))
