Index match inside an If for Google Sheets.

[plant007]

Hi

I am trying to compare a 'work start date' (first column) against the last spend entry in an annual profile, in the case below its 'Prior years spend'. 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. In the case below, the entry is wrong as start of works is in 2034 while last spend is prior to 2023/24. I am using Index Match within an If as follows, and then concatenate a message but always get an #N/A;

=if(INDEX('3. Planned Investment'!$2:$2,MAX(IF('3. Planned Investment'!AD8:AP8<>0,COLUMN('3. Planned Investment'!AD8:AP8),"Prior Years' Spend (£m)",concatenate("As last profile is Prior Years' Spend, CHECK against ", TEXT('3. Planned Investment'!Z8,"DD/MM/YYYY"),"")))))

Be really grateful for any help on this as cant seem to solve it!
Cheers
Andy

 

[plant007]

Thanks, Fluff - happy to post this in General Discussion & Other Applications if better?

im still struggling with the equation (now in excel 2019) and also the ask has changed from DD/MM/YYY to QQ YYYY/YY

The big array equation starts thus - =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 most appreciated!!
Cheers
Andy

 

[Fluff]

As this is now a significantly different question, it needs a new thread. If it's for Excel then it needs to be in the Excel Questions part of the board.

 

[plant007]

As this is now a significantly different question, it needs a new thread. If it's for Excel then it needs to be in the Excel Questions part of the board.

Thanks, Fluff and will do