Need help please with VLOOKUP and INDIRECT question

[Chuck M]

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?

 

[PCLIVE]

Try this:

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

HTH,
Paul

 

[JE McGimpsey]

One way:

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

 

[krcowen]

Chuck

The indirect argument needs to evaluate to an address, and it will if
you do your concatenation inside the parentheses. So,

=VLOOKUP(CurrentMonth,"'"&INDIRECT("A9"&"'!"&$AE$41:$AF$52),2,FALSE)

should work.

Good luck.

Ken
Norfolk, Va

 

[Chuck M]

Perfect! I was so close, yet so far away

Thanks Paul.
--
Chuck M.

Try this:

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

HTH,
Paul