Simple Lookup giving me problems today

[MangoFresh]

Ignore the error that appear. I deleted a lot of rows for this example. But Basically, I need to look up the same row from 2025 & 2024 columns and have it go into the comparison based on the date in E3 of comparison. I cannot get an index/match to work. Your help is always appreciated.

[JohnTopley]

The file is full of #REF! errors!

in H7

=INDEX('2025'!$H$7:$T$15,MATCH($F7,'2025'!$F$7:$F$15,0),MATCH($E$3,'2025'!$H$4:$S$4,0))

Or

=INDEX(INDIRECT("'" & YEAR(E3) & "'!$H$7:$T$15"),MATCH($F7,INDIRECT("'" & YEAR(E3) & "'!$F$7:$F$7"),0),MATCH($E$3,INDIRECT("'" & YEAR(E3) & "'!$H$4:$S$4"),0))

[MangoFresh]

Yeah, sorry for all the #REF! errors. It is because I deleted a bunch of lines that those were calculated on. The problem with the $F7 lookup is because it is listed several times. You will see a heading of "REVENUE"; I have "COGS" & "GROSS PROFIT" that have the same row headings reading down. So when I put the formula down to H18 which also says "New" it will read H7 instead. The team doesn't want to rename the row headings for other purposes.

[JohnTopley]

Your file data finishes at H15 so unless you post a proper example we cannot help further.

[MangoFresh]

Ok, I revised the sample for you all. Basically what we want to is to change the date in Comparison E3 and have the figures in columns H & I change to the appropriate month from the appropriate sheet.

[JohnTopley]

In H7

=INDEX(INDIRECT("'" & YEAR($E$3) & "'!$H$7:$T$15"),MATCH($F$6,INDIRECT("'" & YEAR($E$3) & "'!$F$6:$F$15"),0)+MATCH($F7,INDIRECT("'" & YEAR($E$3) & 

"'!$F$7:$F$15"),0)-1,MATCH($E$3,INDIRECT("'" & YEAR($E$3) & "'!$H$4:$S$4"),0))

in I7

=INDEX(INDIRECT("'" & YEAR($E$3)-1 & "'!$H$7:$T$15"),MATCH($F$6,INDIRECT("'" & YEAR($E$3)-1 & "'!$F$6:$F$15"),0)+MATCH($F7,INDIRECT("'" & YEAR($E$3-1) & "'!$F$7:$F$15"),0)-1,MATCH(DATE($E$3,-12),INDIRECT("'" & YEAR($E$3)-1 & "'!$H$4:$S$4"),0))

in H18

=INDEX(INDIRECT("'" & YEAR($E$3) & "'!$H$7:$T$37"),MATCH($F$17,INDIRECT("'" & YEAR($E$3) & "'!$F$6:$F$37"),0)+MATCH($F18,INDIRECT("'" & YEAR($E$3) & "'!$F$7:$F$37"),0)-1,MATCH($E$3,INDIRECT("'" & YEAR($E$3) & "'!$H$4:$S$4"),0))

in I18

=INDEX(INDIRECT("'" & YEAR($E$3)-1 & "'!$H$7:$T$37"),MATCH($F17,INDIRECT("'" & YEAR($E$3)-1 & "'!$F$6:$F$37"),0)+MATCH($F18,INDIRECT("'" & YEAR($E$3-1) & "'!$F$7:$F$15"),0)-1,MATCH(EDATE($E$3,-12

),DAY($E$3)),INDIRECT("'" & YEAR($E$3)-1 & "'!$H$4:$S$4"),0))