Return value based on two variables - year and month

Dear all

A member (Flame Retired) solved the original thread and he developed the formula in this sheet (Thank-you ). It works wonderfully and now I need to extend it to match on two variables (both year and month instead of just month). I tried to modify the formula in cell E21 and E22 but not successful.

Essentially I have a calendar of expenses and need to be able to report on both the calendar and financial year, which is why I need to build the year into this formula. I am hoping to be able to pull a series of reports together to show summary & detailed views of expenses depending on year and month.

Please see attached.

Your sheet layout is a bit formula unfriendly

See if this method works. (note that this is based on your attached sample, which appears to have had some extra rows inserted where you have added comments to it).

First create 2 named ranges.

Named range 1

Name:- Year
Refers to:- =OFFSET('FIN ACCS'!$F$4,0,MATCH('FIN ACCS'!$C$12,'FIN ACCS'!$F$2:$W$2,0)-1,4,12)

Named Range 2

Name:- Month
Refers To:- =INDEX(Year,0,MATCH('FIN ACCS'!$D$12,'FIN ACCS'!$F$3:$Q$3,0))

Then, in C21 Enter =IFERROR(INDEX($C$4:$C$7,SMALL(IF(Month>0,ROW(Month)-ROW(INDEX(Month,1))+1),ROWS($C$21:$C21))),"")

Which must be Array confirmed by pressing Shift Ctrl and Enter.

In D21 =IF(C21="","",VLOOKUP(C21,$C$4:$D$7,2,0))

In E21 =IF(C21="","",INDEX(Month,MATCH(C21,$C$4:$C$7,0)))

Then drag the formulas down as needed.

Hope that makes sense.

Jason this is just awesome

Brilliant work. Thank-you very very much