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

 

[awoohaw]

i'm trying to parse out your formula. what is the condition the first IF statement is checking? I do not see an expression.

 

[plant007]

Apologies, first IF is meant to find the last annual profile with spend in it and if it is 'Prior Years spend', then produce a message (the reason for this is I cannot compare a start date year against that field as its labelled as text and covers many undetermined years

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[plant007]

Thanks and have updated Account details - although I am on 2019 version, I am actually working at the moment in google sheets for version control / sharing purposes with collegues

 

[Fluff]

I am actually working at the moment in google sheets

In that case the question should be posted in the General Discussion & Other Applications section of the site & you should make it clear what software you're using.
I have done that for you this time.

 

[Fluff]

Try

=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"),""))

 

[plant007]

In that case the question should be posted in the General Discussion & Other Applications section of the site & you should make it clear what software you're using.
I have done that for you this time.

apologies, it will be transferred over to 2019 excel once I have sorted these equations

 

[plant007]

Try

=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"),""))

Many thanks for this and I will try it this afternoon

 

[Fluff]

Ok