Help! VLOOKUP / INDIRECT formula when the data is a formula

Hi,

Please note I am self taught in Excel. I will try best explain the best I can, but don't know the lingo

I am doing a formula (below) which looks up data in a sheet name. The data is a date for example 01.02.22

=VLOOKUP($F4,INDIRECT("'"&AN$2&"'!"&"F4:W300"),18,0)

Cell AN2 is a formula =C2+7

When AN2 is a formula the VLOOKUP formula doesn't work. But when I type the date straight into AN2 it works.

How do I change it so it does work with the formula?

Thanks in advance,

Jenny