INDEX(MATCH Help please

[Taryn]

For some reason I cannot get INDEX(MATCH knowledge to stay in my head!

So, I need a formula in column EH that first looks up if there is a "Y" in column H, if there isn't then return 0
Then to lookup If cell B1 ISERROR return 0

If both the above are true then I need it to return the (SUM of Col DE:DH IF cell B1="Qtr1") or (SUM of Col DI:DK IF cell B1="Qtr2") or (SUM of Col DL:DN IF cell B1="Qtr3") or (SUM of Col DO:DQ IF cell B1="Qtr4")



Also I'm sure there is a very easy way using INDEX(MATCH of making the formula in EG automatic, your help on this would also be great

Thank you in advance!

 

[Debaser]

If you had a row with the quarter names above the relevant columns then you could use INDEX and MATCH but it doesn't really make sense otherwise. As it is, you could just do something like:

=IF(H6="Y",IF(ISNA($B$1),0,SUM(IFS($B$1="Qtr1",DF6:DH6,$B$1="Qtr2",DI6:DK6,$B$1="Qtr3",DL6:DN6,$B$1="Qtr4",DO6:DQ6))),0)

 

[Taryn]

If you had a row with the quarter names above the relevant columns then you could use INDEX and MATCH but it doesn't really make sense otherwise. As it is, you could just do something like:

=IF(H6="Y",IF(ISNA($B$1),0,SUM(IFS($B$1="Qtr1",DF6:DH6,$B$1="Qtr2",DI6:DK6,$B$1="Qtr3",DL6:DN6,$B$1="Qtr4",DO6:DQ6))),0)

Ah ha, amazing thank you so much!!!
Not come across a SUM(IFS before

 

[Taryn]

If you had a row with the quarter names above the relevant columns then you could use INDEX and MATCH but it doesn't really make sense otherwise. As it is, you could just do something like:

=IF(H6="Y",IF(ISNA($B$1),0,SUM(IFS($B$1="Qtr1",DF6:DH6,$B$1="Qtr2",DI6:DK6,$B$1="Qtr3",DL6:DN6,$B$1="Qtr4",DO6:DQ6))),0)

Also any idea how to make the column in EG automated?

 

[Debaser]

What exactly do you mean by 'automated' and which version of Excel do you have?

 

[Taryn]

At the moment, every month I have to manually change the formula to include the current month, I just want the formula to know that it is a new month I have a formula in A1 and then automatically include the new months data

 

[Debaser]

Missed that you were talking about EG not EH!

You can use index/match for that:

=SUM($DE6:INDEX(DF6:DQ6,MATCH($A$1,$DF$5:$DQ$5,0)))

 

[Taryn]

Missed that you were talking about EG not EH!

You can use index/match for that:

=SUM($DE6:INDEX(DF6:DQ6,MATCH($A$1,$DF$5:$DQ$5,0)))

Perfect thank you!!!
I really appreciate your help