One way:

=VLOOKUP(CurrentMonth,INDIRECT("'"&A9&"'!AE41:AF52 "),2,FALSE)

In article ,
Chuck M wrote:

I'm using VLOOKUP to look up a value on another worksheet. The lookup works
fine when I use a constant for the sheet name but I get 'The Formula You
Typed Contains an Error' message when I try to pull the sheet name from a
cell on the current worksheet.

The following works fine
=VLOOKUP(CurrentMonth,'Cost of Goods Sold'!$AE41:AF52,2,FALSE)

Cell A9 on the current worksheet contains the sheet name Cost of Goods Sold.
This formula does not work:
=VLOOKUP(CurrentMonth,"'"&INDIRECT("A9")&"'!"&$AE$ 41:$AF$52,2,FALSE)

Do I have a simple syntax error here or can I not use INDIRECT in this manner?