vlookup, &, indirect

[Joe Blow]

Hi,

I am sure this has been discussed but I am having trouble
understanding what is needed.
I think I need to use a indirect call within vlookup.

Scenario
multiple workbooks with same name except a with a differing date.
ie file 'foo 01.12.03.xls'
worksheet in each file foo called 'auth zoo'
I have a summary workbook with a column with each date: E2 = 01.12.03
also in summary, A1 = 'foo ' and B1 = auth zoo'!$C$11:$I$81 and C1 =
..xls
my lookup value is in A3
what I would like to do, but I am not having too much success is:
vlookup(A3,indirect(A1&E2&C1&B1),7,0)

I would greatly appreciate it if someone could explain the error of my
ways. Thanks.

Regards,
Joe

 

[Peo Sjoblom]

You forgot some things

=VLOOKUP(A3,INDIRECT("'["&A1&" "&E2&C1&"]"&B1),7,0)


will work, I removed the space from A1 and hardcoded it into the formula
after the second ampersand, since it is not visible
it is hard to check the formula for errors, if you want to keep the space

=VLOOKUP(A3,INDIRECT("'["&A1&E2&C1&"]"&B1),7,0)

 

[Joe Blow]

You forgot some things

=VLOOKUP(A3,INDIRECT("'["&A1&" "&E2&C1&"]"&B1),7,0)


will work, I removed the space from A1 and hardcoded it into the formula
after the second ampersand, since it is not visible
it is hard to check the formula for errors, if you want to keep the space

=VLOOKUP(A3,INDIRECT("'["&A1&E2&C1&"]"&B1),7,0)

Thanks Peo!

Regards,
Joe