Logic check - trouble with Index Max

[plant007]

Hi

I am really hoping you can help!

I am trying to compare a 'work start date' (first column - see yellow - Q3 2025/26) against the the field name (YYYY) of the last spend entry in an annual profile, in the case below its '2024/25'. This is to show whether the 'work start date' is before or after the last annual profile spend entry. Basically its a logic check as 'start of works' should not be after the last annual profile spend entry. As 2025 is after 2024, I would show an error message

The big array equation starts thus to handle blanks etc, and the first and last spend as they are aggregated and therefore need a manual check -
=IF(OR(B13=1,'3. Planned Investment'!Z7="",'3. Planned Investment'!AA7=""),"Either, or both, Work Start Date and Date in Service are incomplete",IF(INDEX('3. Planned Investment'!$2:$2,MAX(IF('3. Planned Investment'!AD7:AP7<>0,COLUMN('3. Planned Investment'!AD7:AP7))))="Prior Years' Spend (£m)",CONCATENATE("As last profile is Prior Years' Spend, CHECK against ", TEXT('3. Planned Investment'!Z7,"DD/MM/YYYY")),IF(INDEX('3. Planned Investment'!$2:$2,MAX(IF('3. Planned Investment'!AD7:AP7<>0,COLUMN('3. Planned Investment'!AD7:AP7))))="Remaining spend beyond last known year (£m)",CONCATENATE("As last profile is Remaining spend beyond last known year (£m), CHECK against ", TEXT('3. Planned Investment'!Z7,"DD/MM/YYYY")))))

1. The last part I need to add to the above equation compares 2 parts - =MID('3. Planned Investment'!Z7,4,4) which gives me 2025 which is correct
2. The other part (which I will wrap a Mid around) where the answer should be 2024 gives me #REF! - =INDEX('3. Planned Investment'!$AD$2:$AP$2,MAX(IF('3. Planned Investment'!AD7:AP7<>0,COLUMN('3. Planned Investment'!AD7:AP7))))
3. Ill then add on a concatenated message, to the effect that there is an error as the work starts after the spend has finished

Any help on #2 most appreciated!!
Cheers
Andy