Need help please with VLOOKUP and INDIRECT question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Try this:

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

HTH,
Paul
 
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
 
Perfect! I was so close, yet so far away :-)

Thanks Paul.
--
Chuck M.


PCLIVE said:
Try this:

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

HTH,
Paul
Click to expand...
 
Back
Top