Index, Indirect and Match question

[AlanStrobel]

Why does the 1st formula work but he 2nd give me the REF error.

The '2024' is the sheet name which will increase each year but want to keep previous years.

=INDEX('2024'!$U$4:$BU$7,MATCH(Main!$C3,'2024'!$T$4:$T$7,0),MATCH(Main!$D$2,'2024'!$U$3:$BU$3,0))

=INDEX(INDIRECT("'" & A1 & "'!$U$4:$BU$7"),MATCH(C3,INDIRECT("'" & A1 & "'!$T$4:$T$7",0),MATCH(D2,INDIRECT("'" & A1 & "'!$U$3:$BU$3",0))))

#REF!

 

[Kaper]

Make sure all brackets are in right places.
so you had
some_address
now uou need
INDIRECT(text_of_that_address)

so MATCH(Main!$C3,'2024'!$T$4:$T$7,0)
shall be MATCH(Main!$C3,INDIRECT("textof'2024'!$T$4:$T$7"),0) and so on.

probably something like:

=INDEX(INDIRECT("'" & A1 & "'!$U$4:$BU$7"),MATCH(C3,INDIRECT("'" & A1 & "'!$T$4:$T$7"),0),MATCH(D2,INDIRECT("'" & A1 & "'!$U$3:$BU$3"),0))